Reading Excel Files in UNIX Servers

[jerome_rajan]

Hi,

I've gone through the forum and not found an answer that pertains to our situation.

Our DataStage server sits on a UNIX machine. We are faced with a requirement where we have to read Excel files (.xls/.xlsx) from the server.

I understand that the solution is largely simplified if the server were a Windows server but I am not too sure if there are drivers available for UNIX. If there are, kindly point to a reference on how to install it in the UNIX server.
Also, once installed what is the process of using the ODBC connector to read the files.

Thanks in advance.

[ray.wurlod]

New in version 9.1 is a stage specifically for this task. Upgrade.

Otherwise you will need to track down a third party ODBC driver for Microsoft Excel that runs on UNIX platforms.

ODBC data sources are defined in $DSHOME/.odbc.ini and specified in $DSHOME/uvodbc.config.

[prasannakumarkk]

From where you are getting this excel file. You can better ask the source window team to save their excel in CSV and then put in Unix Box.

In case if you go with ODBC driver, you may be in a place to face issue. Like the empty cell in excel at last may throw issue. Zip code/ columns with zero left padded may have single quote.

Advisable to use CSV.

[Mike]

You could write a script that utilizes the PERL Spreadsheet::ParseExcel module to convert an Excel .xls file into a tab-delimited text file. It avoids the necessity of finding an ODBC driver, and PERL is probably already available on your server... though you may need to have your admin add the module if it's not included in your PERL install.

Mike

[jerome_rajan]

Thank you all for the suggestions. Upgrading doesn't seem feasible at this point. Mike's suggestion is certainly worth a try.