xls.files

vj_meghani · Post by **vj_meghani** » Fri Oct 29, 2004 11:17 am

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.

kduke · Post by **kduke** » Fri Oct 29, 2004 11:27 am

TNS needs to be resolved with sqlplus. If you get on the server and use sqlplus from there and it works then it should work in DataStage. If not then your Oracle home variable is wrong or one of the other Oracle variables.

ray.wurlod · Post by **ray.wurlod** » Sat Oct 30, 2004 2:13 am

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.

metabill · Post by **metabill** » Thu Nov 18, 2004 3:47 pm

(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

metabill · Post by **metabill** » Thu Nov 18, 2004 3:52 pm

It apears I have solved my own problem....

After taking another look at the Import dialog, I noticed a Filter button. After clicking on that, I did see the Include System Tables checkbox. Selecting that and proceeding resulted in the table definitions being loaded OK.

-Bill

mhester · Post by **mhester** » Thu Nov 18, 2004 3:54 pm

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,

vj_meghani · Post by **vj_meghani** » Fri Nov 19, 2004 9:35 am

Hi.
I upgrade Os to XP. I am able to get the data of XLS that is of only one file that i hv show in DSN.

How can i import all file together or how can i add all file together. Is there any way.

thanks

ray.wurlod · Post by **ray.wurlod** » Fri Nov 19, 2004 4:40 pm

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.

ketfos · Post by **ketfos** » Fri Nov 19, 2004 4:49 pm

Hi,

We can save the Excel file has .csv
Then read the file in sequential file stage and load it.

This will be simple and claen.

Ketfos