xls.files
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Fri Oct 29, 2004 10:48 am
xls.files
i am trying to load files from excel but not able to load it. Can anyone help me out in this.
I have TNS listner prob too. how can resolve this porblem.
I have TNS listner prob too. how can resolve this porblem.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Welcome aboard! :D
I note that you are on Windows, this explanation relates to that platform.
To extract data from Excel, the data must be in columnar format with column headings. Set up an ODBC data source using Microsoft ODBC driver for Excel as the driver, and select the workbook that you wish to access.
In DataStage Manager, import the "table definition" from this data source, making sure to check the "system tables" check box. All worksheets are reported by the ODBC driver as system tables; you will get "$" prefixing the name of each. If you only select the one "table" (your worksheet) the Detail and View Data buttons will be enabled, which allows you to test the connectivity.
Once the table definition has been imported, you can use an ODBC stage in a DataStage job to extract the data from the worksheet.
I note that you are on Windows, this explanation relates to that platform.
To extract data from Excel, the data must be in columnar format with column headings. Set up an ODBC data source using Microsoft ODBC driver for Excel as the driver, and select the workbook that you wish to access.
In DataStage Manager, import the "table definition" from this data source, making sure to check the "system tables" check box. All worksheets are reported by the ODBC driver as system tables; you will get "$" prefixing the name of each. If you only select the one "table" (your worksheet) the Detail and View Data buttons will be enabled, which allows you to test the connectivity.
Once the table definition has been imported, you can use an ODBC stage in a DataStage job to extract the data from the worksheet.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
(Using DataStage 7.5 on WinXP/SP2)
Even after following the instructions in the previous post, I still cannot import the table definitions from the sheets in an Excel 2002 worksheet file. The file contains 3 tabular sheets, all with column headings on the 1st line. I've created an ODBC system DSN (MS Excel driver) pointing to the file. When I attempt to use DataStage Manager to import the ODBC Table Definitions, I only get the error "No match found".
FYI: I do not see any option in the Import dialog to check System Tables; perhaps it would come after the point at which I'm getting the error.
Thanks in advance....
-Bill
Even after following the instructions in the previous post, I still cannot import the table definitions from the sheets in an Excel 2002 worksheet file. The file contains 3 tabular sheets, all with column headings on the 1st line. I've created an ODBC system DSN (MS Excel driver) pointing to the file. When I attempt to use DataStage Manager to import the ODBC Table Definitions, I only get the error "No match found".
FYI: I do not see any option in the Import dialog to check System Tables; perhaps it would come after the point at which I'm getting the error.
Thanks in advance....
-Bill
Make sure that when you defined the DSN that you configured it properly and are pointing to a valid Excel document. Also, when you choose import from the manager you should see a button next to your username and password named - Filter >> Choose this option and you will then be presented with more options with one being - "Include System Tables".
Once you do this and choose OK, you should see entries for each tab or sheet from your document. You can then choose to import all none or 1.
Hope this helps
Regards,
Once you do this and choose OK, you should see entries for each tab or sheet from your document. You can then choose to import all none or 1.
Hope this helps
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 7
- Joined: Fri Oct 29, 2004 10:48 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need a separate DSN pointing to each workbook (XLS file).
Within a workbook, each worksheet (tab) is reported via DataStage metadata import as a "system table". It must have column headings and a proper columnar structure to work successfully as a data source.
Within a workbook, each worksheet (tab) is reported via DataStage metadata import as a "system table". It must have column headings and a proper columnar structure to work successfully as a data source.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.