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
Reading data from differnet worksheets(tabs) in excel file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
Pradeep Kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
Pradeep Kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.