Import table in Ms. Excel file to DB2 table

[zhaicaibing]

Hi,

Anyone of you has an idea to import table in an Excel worksheet to a DB2 table?

[ArndW]

You only need to declare your Excel sheet as a system ODBC data source and you are in business.

Fortunately you are on a Windows platform so this is easy; if the DS server is on UNIX and you need to access Excel it becomes a bit more complicated and expensive.

[ray.wurlod]

It is absolutely vital that the Excel worksheet is well-formed for ODBC - that is, it has columnar format and column headings.

Also, when importing the table definition from the ODBC data source, you need to check the "include system tables" box, because worksheets are presented as system tables, indicated by the "$" on the imported table name.

Finally note that the ODBC Enterprise stage can only operate in sequential mode when performing a SELECT.

[zhaicaibing]

Hi,

What if the Excel file has multiple sheet? how can I extract tables in the subsequence sheet?

Please advise. Thanks.

[kcbland]

Check out the perl libraries. They have freely available libraries that read .xls files and normalize the worksheets into separate files.

Otherwise, re-read Ray's last post, because he states worksheets are different tables. You'll need to know the worksheet names to know the table names.

[rajeev_prabhuat]

You only need to declare your Excel sheet as a system ODBC data source and you are in business.

Fortunately you are on a Windows platform so this is easy; if the DS server is on UNIX and you need to access Excel it becomes a bit more complicated and expensive.

Hi,

I have to access MS Excel spreedsheet from UNIX platform what are the complexity that i will have to face to do this activity. As speficid in this post.

Thanks in advance.

Regards,
Rajeev Prabhu

[kumar_s]

HI Rajeev Prabhu,
You can do this by converting existing excel to Comma Seperated Value file format. Then you can access from unix.
But again iam not sure about the easy way to get the each spread sheet into the csv format.

regards
Kumar

[ArndW]

Hello Rajeev,

accessing Excel directly on UNIX is not a builtin feature of DataStage or of UNIX. There are software packages that will let you declare an Excel document to be accessible via ODBC on UNIX, but you need to buy the software. DataDirect is one supplier of this software and there might be some free packages out there that do this (it's worth checking on Google or Yahoo! or your favorite search engine). Also try searching this forum with "UNIX ODBC Excel" to see some more responses to this question.

I initially posted the ODBC definition since you did state that you are on a Windows platform.

[rajeev_prabhuat]

Hello Rajeev,

accessing Excel directly on UNIX is not a builtin feature of DataStage or of UNIX. There are software packages that will let you declare an Excel document to be accessible via ODBC on UNIX, but you need to buy the software. DataDirect is one supplier of this software and there might be some free packages out there that do this (it's worth checking on Google or Yahoo! or your favorite search engine). Also try searching this forum with "UNIX ODBC Excel" to see some more responses to this question.

I initially posted the ODBC definition since you did state that you are on a Windows platform.

Hi,

Thankyou very much for your inputs. I will check as you have mentioned.

One more thing, what about reading multiple sheets from a excel sheet, is this also done through 3rd party tool in unix platform?

Regards,
Rajeev Prabhu

[ArndW]

I think that DataDirect (the most common tool) cannot do this. You'd have to research a bit. If you do find something that can do this, please post it on this thread.