Read data from Excel

[mandyli]

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

[DSguru2B]

How are you accessing the .xls sheet? via odbc or sequential file stage? You need to convert it to csv before reading it via sequential file stage.

[ray.wurlod]

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.

[sachin1]

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.

[ray.wurlod]

NONE of which will work unless you have a UNIX-based ODBC driver for Excel.

[tomd]

Any advice on where to find linux-based ODBC drivers for Excel and Access?

We're running redhat linux and would like to read/write excel files directly (not via csv format or a windows proxy).

[ivannavi]

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=

[tomd]

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.