When setting up a new Excel Workbook file format I do not get the option of selecting which worksheet to use.
I have selected the file directory, selected the file, when I select the worksheet radio button there is a slight delay, then nothing populated, and no worksheet names present in the drop down?
I have had this working previously with this client install on this machine. Point of note: if I login using another machine this works perfectly fine?
Any ideas what’s at fault?
I’ve tries inputting the worksheet name manually, then selecting import metadata, but I get the “OLE or COM processing error. Please make sure Microsoft Access Database Engine is properly installed: . (BODI-1112338)”
I’ve also completed a repair on Data services, with no joy either!
Isn’t it mandatory for DS 14 to have Office 2010 or Office 2010 drivers? I remember I saw this note in the Install Guide… on the very first page itself like a kinda disclaimer statement…
I’ve checked the supported file connectivities, and Excel 2003 is supported as a source, however as you rightly point out this requires the MS Access Database Engine 2010.
For my 32 bit client this should (and probably was) installed when designer was installed. I suspect an Enterprise MS update has been pushed down which may have done something?
I have since uninstalled and re-installed this driver, but still no joy.
I am using Dsesigner 14.0.1.142 and excel 2003. I am having the same problem. I tried reinstallting the microsoft access 2010 database engine and even sp1 of that engine. But that did not work.
I am facing the same problem. Excel access has worked for a while and stopped working for no apperent reason.
My problem is not the job server. The older dataflows with excel work fine.
I can not create new workbook definitions in designer (import metadata).
I tried changing the DI license key, but that did not resolve the problem.
I will try my luck with SAP support. I will keep you informed.
Hi Guy’s sorry for the late reply.
For info. our team is still experiencing this problem with no solution to date.
We have tried re-installing the OLE DB engine from within the BODS Install folders.
We have since had an Office update to 2010.
Neither of which have solved the problem.
I haven’t tried a reinstall of the complete designer as I’m far to busy to do that.
At the moment the only work around I have is to log onto the BODS server (remote connection) and open up designer on there, then import the workbook to my repository.
Once that is done, I can then utilise the workbook in designer back on my client. Bit of a hassle but the only way I can see.
If anyone out there has a better idea or even a solution please share.
i too is having the same issue. i used to be be able to see worksheet\s and get the excel format when setting up flat file (excel) in data services designer. but, now, i am unable to.
I have looked around but it does not seem as if anyone has posted a solution yet, here or on SCN.
To recap, when trying to import an Excel workbook, the worksheet drop down does not display the worksheet names. If the worksheet name is entered manually and the import schema button pushed the error “OLE or COM processing error. Please make sure Microsoft Access DataBase Engine is properly installed BODI-1112338” is displayed.
This is my demo environment oand consists of a Windows 2008 DataCenter server with BOBJ 4 SP5 and Data Services 4.1 FP1. The office is 2010 and I have supplemented the existing Microsoft Office Access database engine 2007 with Microsoft Access Database Engine 2010.
What I do notice is that I can not open an excel document by double clicking on it. I have to open MS Excel and then select file open to open a workbook. I assume this is caused by Live Office as it has often done in the past. I just dont know if this would have an influence on the sheet import function on Data Services.
already noticed this bug which seems to be purely excel relevant. try to find and download the file “Microsoft Fix it 50392” to correct it
in another hand, speaking about excel file, noticed it was not possible to pass as variable a tab name, it doesn’t work. So, I have been obliged to define an area name which work fine as variable. Did you notice such bug ?
Well there goes that idea. Since it doesnt allow me to enter an actual sheet number when I click , I was going to sniff the file to get the sheet name and populate a variable for the sheet name.
this seems like a lot of work to read the first and only spreadsheet in an xlsx file.
I have fixed the bug with opening a spreadsheet by double clicking on the name. This however does not solve the problem with Data Services not being able to import the spreadsheet metadata.
I have not been able to resolve this problem.
I reinstalled MS Office Professional Plus (32bit)
I have reinstalled Data Services
I can setup an ODBC connection to the excel sheet using C:\Windows\SysWOW64 - odbcad32
However when I try to acces it from Data Services, it is not there and when you open ODBC Admin in DS it is clear that it opens C:\Windows\System32 - odbcad32 which of course is the wrong 32bit odbc admin and does not contain the drivers.
I am certain this is the problem but I am not sure how to fix it.
Im running on a client workstation with:
[list]
[:a5c3b93901]Designer 14.0.1.142
[:a5c3b93901]Windows XP Pro SP 3 (32-bit)
[/list]
Test with spreadsheet files:
[list]
[:a5c3b93901]MS Excel Spreadsheet 97-2003
[:a5c3b93901]MS Excel Spreadsheet 2007
[/list]
This issue appears to also be outstanding on the SAP site http://scn.sap.com/thread/3212458
Im bumping this in case anyone has any new ideas. My current thinking is to work around it (as always). Either with CSV files or using the Design tool installed on the server, which for some reason doesnt encounter this issue when dealing with XLSX files.
Hi All. I came to this thread as I encountered the same issue. I eventually solved it.
I was trying to access different tabs on the same worksheet at once. I.e. In one single dataflow, there were separate sub-flows extracting different sets (tabs of the spreadsheet) of data. I separated those into different dataflows and that solved the issue.
I had the same problem I was trying to access multiple excel files and was getting same error as OP. however here are some of the issues I had that I think were causing it to fail.
Column names in the files too long
Sheet name too long or with multiple different characters
Wrapped text in excel
For number 1) I validated the data flow by going to Validation > Validate > Current View and it told me I had a column name that was too long
For number 2) I ran the data flow with only pointing at one sheet to see what error it came up with, originally had been pulling data from multiple files (using wildcard in flat file settings i.e. workbook*.xlsx for ‘Workbook 1’, ‘workbook 2’ etc.) and couldn’t determine what the specific problem was with the excel sheet.
This threw an error saying couldn’t read sheet name or something like that, so changed it from the long name I had to simple acronym of name.
For 3 we were getting lots of delimiter errors and noticed the excel had some cells with wrapped text. Simple fix was to highlight all data, hit the wrap button to wrap all text, then hit wrap again to unwrap all text.
Also make sure files are in excel 97-2003 format as it doesn’t like the later format. It will work majority of times but sometimes chucks an error.
Issue: When I select Import schema, nothing happens.
After three days of research and many various potential solutions, I found what worked for my environment.
It seems when you select Excel workbook in formats and configure an excel source, SAP DS designer in some way caches previous attempts.
I imported a very basic spreadsheet, using a new format, and this seemed to work. I then delete the schema contents, point to another more complex file and select import schema, this doesn’t work.
This is when I realized I need to create a new format every time I select a different excel spreadsheet.
(BOB member since 2011-08-01)
(BOB member since 2008-07-17)
(BOB member since 2011-08-01)
(BOB member since 2012-01-21)
(BOB member since 2008-01-24)
(BOB member since 2003-05-26)