Monday, September 14, 2015

Converting a batch of files of csv format to xls or xlsx

While working with data, we tend to deal a lot with CSV files. It is more efficient and trivial to manipulate csv data either through command line tools or through programming languages like ruby.

However, occasionally we need to convert files to MS Excel format before sending them to 3rd party.
Gnumeric is a wonderful opensource spreadsheet program.  It contains a command line convertion tool called ssconvert that can be used to perform direct conversion among lots of supported formats easily from the command line.

For example, conversion from csv to xlsx can be done via
ssconvert  file.csv file.xlsx
ssconvert  file.csv --export-type=Gnumeric_Excel:xlsx
It can be installed in ubuntu via
apt-get install gnumeric
In order to convert lots of files, I wrote a simple bash script that will process all csv files in the current directory and generate the corresponding xlsx files.  The script came pretty handy in data preparation. I believe i can be helpful to many people

This can be simply downloaded and placed at /usr/local/bin,  and be used directly any where.

No comments: