Not able to read .XLS file from DS

tanaya.deshpande@tieto.co · Tue Mar 15, 2011 3:12 am

HI

I have changed the extention of the .xls file to .csv ..but still I am not able to read the file thru Sequential file stage ..
is there any thing else I need to do..may in format tab..

Thanks

tanaya.deshpande@tieto.co · Tue Mar 15, 2011 3:14 am

My .xls file with changed extension .csv is present in unix box..
I am not able to view it thru cat command also

abhilashnair · Post by **abhilashnair** » Tue Mar 15, 2011 4:21 am

Just by changing the extension, the file format does not change. You need to read the data either as fixed width or delimited file in the sequential file stage.

chulett · Post by **chulett** » Tue Mar 15, 2011 7:08 am

Actually, you need to "Save as" your xls file to another format - .csv, .txt, .prn for example - before you transfer it to your UNIX system. Then you'll be able to read it with the Sequential File stage.

ajay.vaidyanathan · Post by **ajay.vaidyanathan** » Wed Mar 16, 2011 1:02 pm

Hi,
Is your flatfile Delimited or Fixed? Cause if you are modifying your file extension alone, you will not impact the delimiter within the file.

For your delimiter also to be changed you need to manually open your file and change it or have some perl script called through unix and modify the delimiter first and then try using that file in your job.

jwiles · Post by **jwiles** » Wed Mar 16, 2011 1:24 pm

The .xls format (Excel spreadsheet) is a proprietary layout and not generic like .csv. You cannot easily process it directly using a sequential file stage as it also contains formatting information, formulas and other non-data items. As Craig suggested, you (or the data provider) need to save it (from Excel) as a .csv file which should then contain only the data itself. Then you can read it with sequential file stage.

Regards,

ray.wurlod · Post by **ray.wurlod** » Wed Mar 16, 2011 4:05 pm

The alternative is to find a UNIX-based ODBC driver for Microsoft Excel and use ODBC. There are some restrictions, for example the worksheet must be in tabular format with column headings.