I am using a excel file as source from job server. I would like to know how to read this file in excel worksheet format and use it in the mapping.
Archie (BOB member since 2008-06-19)
(BOB member since 2005-09-16)I gone through these but to no help of mine.
I am using DI 11.7. The job server is on unix system whereas I am using DI through windows, i.e, DI and job server are not on the same computer.
The excel file is located on Job server.Even after giving the directory path, filename, I am not able to read the file.
Is the file to be accessed through FTP? or can I use it wothout checking any of the option for Data access since file is located on job server itself.
Please help.
Archie (BOB member since 2008-06-19)
People would be much more able to help you if you give the specific steps you tried and the results of those steps. Did you receive an error message? If not, what happened when you ran the job? What steps did you go through to try to resolve the issue? What specific version of DI are you using (11.7.x.y)? What flavor of unix is the job server? etc. The more information you give the more likely we are to be able to help you.
- Ernie
eepjr24 
(BOB member since 2005-09-16)
DI 11.7.2.3
The job server is on Unix env. On this system, I have copied the file and tried to use this file through DI. I mentioned the directory, filename, worksheet in the Format tab and nothing checket in Data Access tab. The Import Schema tab is not enabled, so I manually added the schema. On executing the job, I get the error:
Is the adapter to be created and configured in job server?
Is this the only way how I can use excel workbook or there is any other way also to use it?
Archie (BOB member since 2008-06-19)
Looks like the excel adapter is not set up. Werner, didn’t you have a tutorial for that somewhere?
The only other ways I know to do it would be export the excel to CSV or use one of the various ODBC products for Excel on Unix.
- Ernie
eepjr24 (BOB member since 2005-09-16)
Never did that myself…
Werner Daehn 
(BOB member since 2004-12-17)
Bah. I have not either. I just mandate that they have to get the data to me in CSV or fixed width format. Excel has too many user created problems for my tastes.
Anyone else have experience setting it up and cares to share? Maybe even post a nice article over on the SAP portal?
- Ernie
eepjr24 (BOB member since 2005-09-16)
Is that I need to set up an excel adapter on Job server?
I again list the steps that I am following and the let me know if I am missing anything:
- Copied the excel file on on system whrer I run DI
- In the Object Format, a new excel workbook format is created
- In the Format tab, given the Directory: C:\Documents and Settings\xxxxx\Desktop\Flat File
File Name:xxxxx.xls
Work sheet: xxxxxx - Then clicked on Import Schema. The schema was available and then did changes in it.
- Clicked OK
- On executing the job, the following error comes:
20949 1 RUN-058105 10/1/2008 5:02:51 AM |Dataflow xxxxxxxxx
20949 1 RUN-058105 10/1/2008 5:02:51 AM Error preparing to read : .
Archie (BOB member since 2008-06-19)
Hi,
this is one of the post where Ernie has mentioned this
"Better yet, don’t create stuff in Excel! (Tongue stuck firmly in cheek there). Sorry, just could not resist. Our users run into WAY too many problems due to the vagaries of MS Excel. "
I am using the excel files as source in my mappings. I would like to know what could be the possible complexities in using it. What could be the errors in reading it? What all things I need to test for it?
Thanks in advance
Archie
Archie (BOB member since 2008-06-19)
The biggest problems I have encountered are with user created excel files. They tend to be inconsistent in their population of data i.e. blank column values with a row, blank rows in the middle of the file, columns skipped for “spacing”. They can also have formatting to make things read well that show up as invalid (non-displayable) characters, contain unusual characters sets (cut and paste from the web or other sources), left over values from people checking their work with sums and other formulae, etc.
A data warehouse is designed to provide consistent, accurate data with transparent lineage and auditability. IMNSHO, MS Excel does not lend itself well to those goals as an input mechanism. There are cases where it is the easiest method, but it can cost a lot of time in the long run.
- Ernie
eepjr24 (BOB member since 2005-09-16)
Hi,
Thanks Ernie!
I am facing one problem in the excel file. The data in the cells is of general format. When I debug the job, I find the value 100 is coming as 100.0 because of this the join condition is not satisfied and hence the result is not coming correctly. Though I have change the cell format to be text but that also doesn’t work. Can I have a solution for this?
Its urgent so can I expect a quick answer for this.
Archie (BOB member since 2008-06-19)
I was able to do it.
Archie (BOB member since 2008-06-19)
Glad you got it figured out. Did you set the input field type to integer? Or format the value in Excel?
- Ernie
eepjr24 (BOB member since 2005-09-16)
Yes. Jobserver has to have the flag “support adapters” turned on and in webadmin the Excel Adapter has to be installed to support that on Unix.
Werner Daehn 
(BOB member since 2004-12-17)
Hi wdaehn,
Do we need any Adapters for Excel to be a source on windows 2003 server?
Thanks
ay1224 (BOB member since 2007-07-30)
No, just for Unix.
Werner Daehn (BOB member since 2004-12-17)
Thank you so much Wdaehn,
but why we are getting the error
ole or com processing error External table is not in the expected format. we are using version 12.1.1.0.
Thanks
ay1224 (BOB member since 2007-07-30)
Hi Archie/Others,
I was looking for some guidance as to how to load data with an Excel file as source.
I went through the forum, but I couldn’t find how to setup the adapter, or how to check if the adapter settings are correct, etc.
Details:
BODS Job Server & Designer - 3.1
Designer on Windows
Job Server on Solaris
What was I able to do so far?
I was able to create the excel file format - then point it to the copy of the input file (file name: Example.xls) on my local machine and get all the field details automatically (FormatName: Excel_Load)
Then I copied the file on the job server and changed the Directory/File Name details in the file format relative to the job server. But I encounter the error “Job Server <> defined for adapter datastore <Excel_Load> is not found in the repository. Please select a valid Job Server in the Datastore editor dialog”
Could you give me some guidance (preferably the steps you followed) to overcome this error and successfully read from the excel file, please?
Thanks,
Srini
srinivas_t (BOB member since 2007-11-25)