Hi
I want to load the data from Excel sheet into DB2 DATABASE.
Directly i have used .xls file into Sequential stage and try to load but that is not working out. Any other ways to load excel data into database.
Thanks
Man
Read data from Excel
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
An XLS file is not amenable to being read by a Sequential File stage; for a start it potentially contains multiple worksheets.
If you have a UNIX-based ODBC driver for Excel you can use that. None ships with DataStage. Otherwise you need to save the worksheet as a text file (.TXT or .CSV) and process that.
If you have a UNIX-based ODBC driver for Excel you can use that. None ships with DataStage. Otherwise you need to save the worksheet as a text file (.TXT or .CSV) and process that.
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.
Re: Read data from Excel
below are the steps for reading .xls file.
for example in my case i have a data like
12 hello
51 hello
13 hello
43 hello
15 hello
21 hello
58 hello
60 hello
34 hello
89 hello
in two seperate columns of sheet.
1. Import the file structure from "sequential file Definitions".
2.you will get a window of "IMPORT Meta Data", in this select file type as all files *.*,select your .xls file.
3.After clicking on import button it will ask you to define sequential meta data,select delimited by tab.
4.See in define tab you get your required columns like Field001,Field002 with its datatype defined, you will be able to preview your data.
5.In sequential file stage give the proper name and location of your file.
6. In format tab give the delimeter as (009) horizontal tab, view your data and do further processing.
for example in my case i have a data like
12 hello
51 hello
13 hello
43 hello
15 hello
21 hello
58 hello
60 hello
34 hello
89 hello
in two seperate columns of sheet.
1. Import the file structure from "sequential file Definitions".
2.you will get a window of "IMPORT Meta Data", in this select file type as all files *.*,select your .xls file.
3.After clicking on import button it will ask you to define sequential meta data,select delimited by tab.
4.See in define tab you get your required columns like Field001,Field002 with its datatype defined, you will be able to preview your data.
5.In sequential file stage give the proper name and location of your file.
6. In format tab give the delimeter as (009) horizontal tab, view your data and do further processing.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Unless you will be reading a lot from Excel, you should host the file on some windows box and convert it to something else. Read this:
viewtopic.php?t=109793&highlight=
viewtopic.php?t=109793&highlight=
Thanks for your timely response.
We currently use open source CPAN Perl modules to manipulate .XLS files. We are quite surprised to find that DataStage does not offer support for this widely used file format. Since we exchange spreadsheets with many customers, both internal and external, we cannot dictate the use of CSV format.
I am new to DataStage. From reading various forums I wonder if a DB2 federated server might offer a solution. If that seems like a valid approach I welcome any "how-to" pointers on using DataStage with a federated server.
We currently use open source CPAN Perl modules to manipulate .XLS files. We are quite surprised to find that DataStage does not offer support for this widely used file format. Since we exchange spreadsheets with many customers, both internal and external, we cannot dictate the use of CSV format.
I am new to DataStage. From reading various forums I wonder if a DB2 federated server might offer a solution. If that seems like a valid approach I welcome any "how-to" pointers on using DataStage with a federated server.