Hi,
Is it possible (and how to) extract data from a Microsoft Excel file (.xls) using DataStage? I am running DataStage v5.2.1 for AIX (5.2)
Thanks a lot.
Dick
Reading Excel on AIX
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Check through the archives for advice relating to ODBC access from UNIX. It is possible, but you have to have a UNIX-based ODBC driver that allows access to the Microsoft environment. These have to be purchased. A couple of vendors are mentioned in the archives.
Tip: When you are importing "table definitions" from Excel, make sure that you include system tables. Individual sheets in a workbook are, for some reason, regarded as system tables!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Tip: When you are importing "table definitions" from Excel, make sure that you include system tables. Individual sheets in a workbook are, for some reason, regarded as system tables!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
If this is a one off extract you may find it easier to save the file as csv and ftp it to Unix. You could also have a look at a site like www.freeware.com and do a search for Excel export products that automate this task. You may find a utility that exports your Excel into XML or CSV format which DataStage can ftp and read. XML Converter Standard Edition 3.43 for example.
Vincent McBurney
Data Integration Services
www.intramatix.com
Vincent McBurney
Data Integration Services
www.intramatix.com
Hi Dick,
reading Excel-files is in most ETL-environments a request. Most project I have seen do this via an export into a *.csv-file (Info for all non-anglo people here: If you do an export via a VBA-automation the anglo-type of *.csv is taken, that is comma as field seperator and point as decimal seperator, if you do it manually the national field seperator is taken in Germany that is semicolon and the nation decimal seperator is taken, in Germany that is comma, so do it one way all time and do not change manual and automatic) other problems are, that people write above and beside cells their comments, so you get changing numbers of fields.
If you are mighty enough for the end-users you have for to fill tables manually for you, let them write in a table in the prezone of your database or even into MS-ACCESS, you can setup checks of validation and you save a lot of work. On my own expierence I can tell you, that if you don't have 100% diciplined excel-users, you will get a lot of problems with excel files and you should never blame datastage for this, because users often use the broad possibilities excel gives them.
Wolfgang Huerter
=====================
Cologne, Germany
reading Excel-files is in most ETL-environments a request. Most project I have seen do this via an export into a *.csv-file (Info for all non-anglo people here: If you do an export via a VBA-automation the anglo-type of *.csv is taken, that is comma as field seperator and point as decimal seperator, if you do it manually the national field seperator is taken in Germany that is semicolon and the nation decimal seperator is taken, in Germany that is comma, so do it one way all time and do not change manual and automatic) other problems are, that people write above and beside cells their comments, so you get changing numbers of fields.
If you are mighty enough for the end-users you have for to fill tables manually for you, let them write in a table in the prezone of your database or even into MS-ACCESS, you can setup checks of validation and you save a lot of work. On my own expierence I can tell you, that if you don't have 100% diciplined excel-users, you will get a lot of problems with excel files and you should never blame datastage for this, because users often use the broad possibilities excel gives them.
Wolfgang Huerter
=====================
Cologne, Germany