How to pull data from EXCEL?

[DataStageCnu]

Can u give the value of "Tab" so that i can pull data when it is seperated by Tab Space.

And one more, i am trying to pull data from Excel sheet. Suppose the data is in the following format.

~~~~~ dealer1 dealer2 dealer3
month01 01d1 01d2 01d3
month02 02d1 NULL 02d3
month03 03d1 03d2 NULL
month04 04d1 04d2 04d3
month05 05d1 05d2 05d3

1.) Can u tell me how to pull the data of four columns from Excel sheet to Flat file or DB?

2.) The next task is --- from excel or seq, the data should arrange in the following order
month01 dealer1 01d1
month01 dealer2 01d2
month01 dealer3 01d3
month02 dealer1 02d1
month02 dealer2 NULL
month02 dealer3 02d3
month03 dealer1 03d1
month03 dealer2 03d2
month03 dealer3 NULL
and so on.....

I tried Pivot Stage, but i am not able to get column headers as columns (Dealer1, dealer2 and dealer3). Remember number of columns are not constant.

Hope you understand my questions.

with regards,
Cnu

[ogmios]

1) Always convert Excel to .csv format outside of DataStage. Write a VBA script or something in Perl that extracts the data to .csv file and then load as a regular sequential file. It will you tons of times.

Whatever you do, don't do it manually. Excell is pretty bad at keeping consistent output if you export to .csv format (especially if the last columns of a row are not always filled in).

2) 1 input link in a transformer and 3 output links from the transformer to your output selecting different data from your rows per link: dealer1, dealer2, dealer3 will probably have to hardcoded as fixed fields.

Ogmios

[ray.wurlod]

Given that you're on Windows, you can use the ODBC driver for MS Excel. In this case, you must check the "system tables" box when importing the table definition (each spreadsheet in a workbook is reported as a system table). Further, the spreadsheet MUST have a tabular format and must have column headings.
Then, if all these conditions are met, you can access the spreadsheet using an ODBC stage.