How to split an ODS spreadsheet file into csv files per sheet on the terminal?
Solution 1:
Well, libreoffice
can convert documents from a script (i.e. in headless mode without opening a GUI). To convert any spreadsheet format to CSV, its simplest form would look like e.g.
libreoffice --convert-to csv PATH/TO/YOUR.ODS
However, this just takes the first sheet of your document and converts that, ignoring all others. It also lacks an option to select the sheet to convert, sadly.
So we're going to need an external tool, like xlsx2csv. It's an open source Python (both 2 and 3) script that converts XLSX files to CSV, and supports extracting all sheets into separate files.
Ubuntu already comes with Python installed, but maybe you need to install pip
first, its package manager. I'm going for Python 3, but you could change all commands below to run it with 2 as well:
sudo apt install python3-pip
Then you can install xlsx2csv
with pip3
into your user's package directory, using
pip3 install --user xlsx2csv
After that, the executable script can be found in ~/.local/bin/xlsx2csv
.
Now if you don't have it in XLSX format already, let's convert that ODS spreadsheet with libreoffice
:
libreoffice --convert-to xlsx PATH/TO/YOUR.ods
Then we use xlsx2csv
to extract all sheets. It will create a folder OUTPUTFOLDER
and place all extracted SHEETNAME.csv
in there:
~/.local/bin/xlsx2csv -a YOUR.xlsx OUTPUTFOLDER
Solution 2:
Using xls2csv
(to convert .XLS) tool of catdoc
package install sudo apt install catdoc
:
xls2csv -b '
' EXCEL.xls | awk '{print >"sheet"NR}' RS='\n\n'
This xls2csv -b '
' EXCEL.xls
reads MS-Excel file and puts its content as comma-separated data on standard output and with -b STRING
we are telling sheets to delimited with what characters (or string; which we defined a actual newline here).
Use xlsx2csv
(to convert .XLSX); install sudo apt install xlsx2csv
:
xlsx2csv -s 0 EXCEL.xlsx |
awk '!/,/ { nextSheet++; next } { print >"sheet"nextSheet }'
The -s 0
means print all sheets.
Solution 3:
Take a look at
ssconvert -O 'separator=:: format=raw quoting-mode=never' -S x.ods out%n.txt
\thanks{Bruni}
-
-O 'separator= format= ...'
is used to control the csv format details -
-S
to create a different output file for each sheet