Converting spreadsheets to text files with ssconvert

ssconvert is a utility shipped with Gnumeric, and it's a command line spreadsheet converter.

Let's say that you have some data in a spreadsheet (eg spreadsheet.ods) and want to be able to easily parse it, and maybe use it in a script.

 
ssconvert -O 'separator=;' spreadsheet.ods parsable-data.txt
 

If the spreadsheet has more than one tab, you can add the "-S" option.
This will create a different text file per tab.

If you want to give these files meaningful names, you can add "%s" in the file name template:

 
ssconvert -S -O 'separator=;' spreadsheet.ods "parsable-data.%s.txt"
 

And, you would end up with files like "parsable-data.tab1name.txt", "parsable-data.tab2name.txt" etc..

Possible problems:
1) I had some warnings/errors when trying to import the file as spreadsheet.xlsx, so I converted it to .ods with Libreoffice Calc first.
xlsx is a Gnumeric supported format so I'm not sure what the issue was, I didn't investigate further.

2) Make sure to use a column separator which isn't already used in the spreadsheet, otherwise parsing correctly would become impossible.
One way to do this is using two characters as separator like:

 
ssconvert -O 'separator=<>' spreadsheet.ods parsable-data.txt
 

Then, when parsing the text file, you can tell awk what separator to use with "-F".
For example, to only print the first and third column:

 
awk -F '<>' '{print $1, $3}' parsable-data.txt
 

3) Make sure to remove all line breaks from cells.
I've done that with Libreoffice Calc enabling regular expressions and replacing all "\n" with an empty string.

Hope it helps!
Andrea

Leave a Reply

Your email address will not be published.