Working with excel

[chandra_babu_999]

can somebody tell me the steps to load xl file and to read the data from xl file?

Do we need any plug-in load the data into xl?

[ray.wurlod]

Presumably you mean Microsoft Excel - why not say so?

You need an ODBC driver for Microsoft Excel. None is supplied with DataStage, therefore you will need to source this yourself.

Otherwise contemplate working with CSV files.

[vmcburney]

You are on Unix and access Excel is very difficult. Unix just doesn't know what an Excel file is and any ODBC driver would lack the Windows libraries needed to read it. You need some type of ODBC bridge from Unix to a Windows server - or just exchange data via CSV of XML files. If you are on DataStage on Windows it becomes easier - you just read Excel as a worksheets as a data table via an ODBC connection.

[ppavani_km]

First your question is not clear to me.
First you need to change the Excel file as .CSV file.
IF you are on Unix server you need to FTP the file(.CSV file) from windows to Unix.
and after that you can use directly sequential file stage and provide delimiter as , and quote as none.

try it out.

can somebody tell me the steps to load xl file and to read the data from xl file?

Do we need any plug-in load the data into xl?

[ray.wurlod]

First you need to change the Excel file as .CSV file.

This is not true if you have an ODBC driver for Excel.

[4492_dsnoel]

try this one..
you need a sequential file(import the metadata) then link it to a copy file and load it to your target file.

[xch2005]

Hi,

To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it

in Stage -> Properties:

1. Java additional classpath - /<path>/jxl.jar

2. Transformer class name - ExcelRead

3. User's custom properties is to be as,

File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>

Try this out, this definitely works in 7.5.

Thanks

[xch2005]

Hi,

To read a excel file from DataStage you can use the jxl.jar (you need to download) and the class used is ExcelRead in it

in Stage -> Properties:

1. Java additional classpath - /<path>/jxl.jar

2. Transformer class name - ExcelRead

3. User's custom properties is to be as,

File=<path>/<filename>
Sheet=1
TopLeft=A1 <cell where data starts>
BottomRight=B <cell where data ends, there is only cell name>

Try this out, this definitely works in 7.5.

Thanks

[xch2005]

In addition to previous post, if I remember correclty the input excel needs to have only 1 sheet.

Thanks

[dstest]

Can you please in which datastage stage i need to set the properties c you mentioned. where i nedd to jxl.jar package.

Thanks

[ray.wurlod]

In addition to previous post, if I remember correclty the input excel needs to have only 1 sheet.

Thanks

That is not the case if you use ODBC. You create a DSN to point to the workbook; each worksheet is a "table" within that DSN.

[xch2005]

When you have jxl.jar it is not connected viz. ODBC, directly java input stage is used in parallel job.

Thanks