Converting huge xls(having multiple tabs) to csv

Shell Programming and Scripting
1

hello

I have browsed for the similar requirement i found this

but my problem is i have multiple tabs in xls file having same metadata

I want to convert it into single csv file

any ways to do it pls suggest

Note : I tried saving file by save as to csv but this will only save current tab to csv not all tabs

thanks in advance

2

Are you comfortable w using Perl? There are a few modules that parse/read-write excel formats.

3

thanks for quick reply but I will prefer unix scripts as I am not that much confortable with perl

4

I have used a tool xls2csv from the package 'catdoc' successfully before.
I believe it separates tabs with some special character, ^L (octal 014) I. believe. I think you need a xls version from office XP/2003, not the newest xlsx stuff.

You could try this to separate the tabs:

xls2csv excelfile.xls  | awk '{print > "tab"NR".csv"}' RS="\014"

This should create files tab1.csv, tab2.csv, etc. one for each tab.

5

Thanks for this useful post but I have very limited privilege to access tools that's the main reason I am looking for any unix script base solution if possible

6

That's too bad. Making sense of the MS Office format is not a trivial task...
Have you looked at this?
XLS to CSV [Archive] - FedoraForum.org

It describes how to create a macro, to save all tabs as csv.

---------- Post updated at 02:22 AM ---------- Previous update was at 01:51 AM ----------

How much privilege do you have? If you have a directory with write access and basic build tools (automake), you can download the source tarball, install the catdoc to the local dir, and use it.

e.g.:
I have +wx access to ~/mystuff. Then:

wget http://fossies.org/unix/misc/old/catdoc-0.94.2.tar.gz
tar xvzf catdoc-0.94.2.tar.gz
cd catdoc-0.94.2
mkdir build && ./configure --disable-wordview --with-install-root=${PWD}/build
make && make install

Then create a file ~/.catdocrc and put some config paths in there:

cat >  ~/.catdocrc <<EOF
charset_path=${HOME}/mystuff/catdoc-0.94.2/build/usr/local/share/catdoc
map_path=${HOME}/mystuff/catdoc-0.94.2/build/usr/local/share/catdoc
source_charset=cp1252
target_charset=8859-1
unknown_char='?'
EOF

And you should be good to go:

~/mystuff/catdoc-0.94.2/build/usr/local/bin/xls2csv myfile.xls
1 Like