Reading data from differnet worksheets(tabs) in excel file

[pradkumar]

Hi

I am required to read data from different worksheets (tab) in a excel file and write to a table. I tried to develop a DS job but its just reading data from first tab. The data type and number of columns are same in tabs

Could any one let me know how to accomplish this?

Thanks

[ray.wurlod]

The ODBC table name should be the same as that of the "system table" whose metadata you imported (including the "$" character).

[pradkumar]

Hi ray
Thanks for ur reply.
As you said the tabs got exported in the form of system tables with $.
This corresponds to only the tabs in File 1. I used a user defined query to read data from tabs and write to a table.
But what shall be done if there is a file2 with the three tabs. Its not possible to automate the process. Do i need to always import the metadata while the job is running. Is there any way to automate this?

Thanks

[ray.wurlod]

No, you can not make it dynamic. The table name can be a job parameter but this would only be useful if each table (worksheet) had the same metadata.

[pradkumar]

Hi

The metadata in all the worksheets will be the same. The only issue is that the number of worksheets can be changed at any time by the Business user.
File1 can have 3 worksheets, File 2 can have 5 worksheets.
So i am looking whether this process can be automated in data stage so taht it picks up new worksheet autoamtically.

is there any way that we can read all the names of worksheets and store it in file in Data stage. Else do we need to use any scripting language for this?

[ray.wurlod]

Answer this yourself. When you set up a DSN does it refer to a workbook or to a worksheet?

If it refers to a workbook then what you propose is probably do-able, most easily with a Routine that calls SQLTables() - a BCI function.