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 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?
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?
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?
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.
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".
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.