Can DataStage read data from Excel Files with multiple tabs
Moderators: chulett, rschirm, roy
-
Inquisitive
- Charter Member
- Posts: 88
- Joined: Tue Jan 13, 2004 3:07 pm
Can DataStage read data from Excel Files with multiple tabs
An excel file to be processed has information in several tabs. The need is to read data from two of the tabs. Is it possible to do that using datastage? If not, can you recommend a solution ?
Re: Can DataStage read data from Excel Files with multiple t
It is not a bad idea to export your spreadsheet to CSV files, one for each tab. Then use DataStage to process CSV (same as sequencial) files.
Inquisitive wrote:An excel file to be processed has information in several tabs. The need is to read data from two of the tabs. Is it possible to do that using datastage? If not, can you recommend a solution ?
Re: Can DataStage read data from Excel Files with multiple t
And preferably write a VB macro for it or extract the data via a perl script, at least something that can be executed automatically. Excell is notorious for it's manual CSV output: e.g. when nothing is filled in the column of a row this column is not generated in the CSV file, ...chunsli wrote:It is not a bad idea to export your spreadsheet to CSV files, one for each tab. Then use DataStage to process CSV (same as sequencial) files.
...
Ogmios
Re: Can DataStage read data from Excel Files with multiple t
For database records, null value is a valid value.
Please do not pad with space, etc. Otherwise, you may have problems later on.
Please do not pad with space, etc. Otherwise, you may have problems later on.
And preferably write a VB macro for it or extract the data via a perl script, at least something that can be executed automatically. Excell is notorious for it's manual CSV output: e.g. when nothing is filled in the column of a row this column is not generated in the CSV file, ...
Ogmios
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you can get an ODBC driver for Excel (I know they exist in the Windows environment but am not sure about UNIX), then you create a DSN that refers to the workbook (the ".xls" file). Each worksheet in the workbook is a table.
When importing the table definitions, make sure you check the "system tables" check box; worksheets are, for some strange reason, reported as system tables. They will have a "$" character added to the worksheet name to indicate this. It's important, too, that the worksheet has column headings and is in a tabular format.
When importing the table definitions, make sure you check the "system tables" check box; worksheets are, for some strange reason, reported as system tables. They will have a "$" character added to the worksheet name to indicate this. It's important, too, that the worksheet has column headings and is in a tabular format.
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.
To read Database-like information from Excel is not an easy task. The people who fill the information to excel do have to have a high grade of discipline, though Excel itself is a very flexible tool. Even in some regions the export via a VB-macro from excel and via manual export does look completly different.
It is very easy and cheap to tell people to fill excel sheet with Information, but thereafter you may have a lot of manual tasks and sometimes a lot of inattended tasks to do.
Better you give your users any other DB-system as source for filling.
For example Access or others.
You have a bit more work to provide, but you can harvest the benefit within month.
Wolfgang
It is very easy and cheap to tell people to fill excel sheet with Information, but thereafter you may have a lot of manual tasks and sometimes a lot of inattended tasks to do.
Better you give your users any other DB-system as source for filling.
For example Access or others.
You have a bit more work to provide, but you can harvest the benefit within month.
Wolfgang