Convert XLS to CSV in UNIX

just4geeks · Post by **just4geeks** » Tue Jan 09, 2007 10:12 am

I need to grep for a string in a XLS file. And that is why, I need to convert it to a CSV file in a script or something. Is there any UNIX command that does that?

Thanks for your help.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 10:36 am

By XLS you mean Excel spreadsheet file? If that's the case, you can use perl dbi functionality to do a conversion. I don't think there's native unix support for specific application data files.

just4geeks · Post by **just4geeks** » Tue Jan 09, 2007 10:47 am

kcbland wrote:By XLS you mean Excel spreadsheet file? If that's the case, you can use perl dbi functionality to do a conversion. I don't think there's native unix support for specific application data files.

Thanks for your response.

Yes, I meant Excel spreadsheet file. Can you please explain a bit more on the perl dbi functionality? I believe we can run perl scripts in korn shell. Can't we?

kcbland · Post by **kcbland** » Tue Jan 09, 2007 11:21 am

There are free libraries available that can be used in perl scripts, which look amazingly like korn shell scripts, that flatten XLS spreadsheets into CSV files for the worksheets contained therein. They will materialize data out of cells containing functions, as well as do other things. You're on your own on this one, I can't help you much getting it running. There's folks here who've done this and can speak to it more clearly.

You're in for a lot of work, because worksheets don't necessarily translate into CSV (rows of columns) because they're not tightly constrained data models.

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 11:23 am

Why not just save the xls sheet as csv and then use it. Who ever is responsible for creating these sheets, request them to send you a csv copy. Usually 'they' dont mind.

Cowmix · Post by **Cowmix** » Tue Jan 09, 2007 12:05 pm

kcbland wrote:There are free libraries available that can be used in perl scripts, which look amazingly like korn shell scripts, that flatten XLS spreadsheets into CSV files for the worksheets contained therein. They will materialize data out of cells containing functions, as well as do other things. You're on your own on this one, I can't help you much getting it running. There's folks here who've done this and can speak to it more clearly.

You're in for a lot of work, because worksheets don't necessarily translate into CSV (rows of columns) because they're not tightly constrained data models.

In the past when I have had to extract data from XLS files from within a UNIX environment I used OpenOffice in command line mode. Read this for more details:

http://www.xml.com/pub/a/2006/01/11/fro ... ffice.html

just4geeks · Post by **just4geeks** » Tue Jan 09, 2007 12:52 pm

DSguru2B wrote:Why not just save the xls sheet as csv and then use it. Who ever is responsible for creating these sheets, request them to send you a csv copy. Usually 'they' dont mind.

I could ask them to send me a csv file. I am trying that too. I thought if there is a command to convert xls, then it would be cool to learn. All I need to do is grep for a string. Since the grep command didn't accept a xls file, I need it converted into something that can be understood by grep? All I need is the text. I don't care if the layout is messed up.

thanks for your time.

narasimha · Post by **narasimha** » Tue Jan 09, 2007 1:01 pm

You are rght, you cannot grep an xls file, You need to get the file in a different format like csv-
If your company is willing to make a purchase, there are many products in the market which can convert xls to csv file, even through the command line.

kcbland · Post by **kcbland** » Tue Jan 09, 2007 1:18 pm

Your problem is the file is binary of a proprietary format, not ASCII text.