How to read Microsoft Excel file as data source?

em2006 · Post by **em2006** » Fri Nov 24, 2006 9:56 am

I am trying to create a simple job that reads from an excel spreadsheet and do a little transformation, and then write the data out to a fixwidth text file. I created an ODBC DSN by using Microsoft Excel Driver, but I got "No data match" when I was importing the table definition. I did specify the excel file in the DSN, can anyone point me where I missed and how to correct please?

Thanks in advance!

ArndW · Post by **ArndW** » Fri Nov 24, 2006 12:13 pm

Did you declare it as a system DSN? I usually double-check ODBC connectivity using the (free) tool MS QUERY; try seeing if that works.

em2006 · Post by **em2006** » Fri Nov 24, 2006 1:18 pm

Thanks for your reply

Yes, it is system DSN, now when I try use MS Query, I got "This data source contains no visible tables.."

I can open the excel file and see data, am I missing something?

ArndW · Post by **ArndW** » Fri Nov 24, 2006 1:29 pm

data does not always equate to tables. Try a simple 1-worksheet file with 2 columns (with headers) - does that work? If so, then what is your data format in your non-working excel sheet?

em2006 · Post by **em2006** » Fri Nov 24, 2006 1:59 pm

I reformatted each column on the simple test file, it worked in MS Query, but still has no luck in DS job. I noticed that there is no microsoft office installed on my DS server, could this be the reason for the failure of importing excel table definition?

Thank you so much!

ArndW · Post by **ArndW** » Fri Nov 24, 2006 2:19 pm

The DSN needs to be defined on the DataStage server, not your client. The Excel file also needs to be read from server as that is where the DataStage job is actually running.

em2006 · Post by **em2006** » Fri Nov 24, 2006 2:30 pm

Both the excel DSN and Data File are defined on the server. When I import I first pick the DSN, left username/password empty and then pick the NLS map, but I get "no match data".

Thanks for all your help!

ray.wurlod · Post by **ray.wurlod** » Fri Nov 24, 2006 3:29 pm

Check the box that says something like "include system tables". For some strange reason (all complaints to billg@microsoft.com) the ODBC driver for Excel treats worksheets as system tables. They come in with "$" prefixes.