7

I would like to split a spreadsheet (ods or xlsx) into multiple csv files, one for each sheet in the spreadsheet.

I would like to do this without launching a graphical app and preferably in a one liner.

Any ideas?

Though the linked duplicate provides a possible solution in one of the answers (not in the accepted one) and was helpful. The solution does not work with ODS files as I requested, and I consider the question to be sufficiently different.

K7AAY
  • 16,864
  • 9
  • 45
  • 77
Bruni
  • 10,180
  • 7
  • 55
  • 97
  • 7
    Possible duplicate of [Convert .xls/.xlsx spreadsheets to multiple .csv's based on a list](https://askubuntu.com/questions/973534/convert-xls-xlsx-spreadsheets-to-multiple-csvs-based-on-a-list) – user68186 Jun 01 '18 at 13:26

3 Answers3

8

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
Byte Commander
  • 105,631
  • 46
  • 284
  • 425
  • apt-get install xlsx2csv already returns a package, any reason I should not try that? – Bruni Jun 01 '18 at 13:42
  • Ok, I did us and and it works as you describe – Bruni Jun 01 '18 at 13:57
  • 1
    @Bruni Good observation, I didn't realize it's in the repositories. However, as always, the Ubuntu package is quite outdated (version 0.20 from 2012 in the Ubuntu repos vs. version 0.7.3 (don't question that numbering scheme...) from 2017 as Python package on PyPI). I tested with the PyPI version, you might possibly run into already fixed bugs with the packaged version, don't know. Probably it works just as well most of the time. You can have a look at the [changelog](https://github.com/dilshod/xlsx2csv/blob/master/CHANGELOG). – Byte Commander Jun 01 '18 at 13:57
4

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.

αғsнιη
  • 35,092
  • 41
  • 129
  • 192
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
  • Strangely this did not work with the xlsx file I tried, and it converted only the last sheet when used with ods. – Bruni Jun 01 '18 at 14:26
  • Undefined number format id '43' Undefined number format id '41' Undefined number format id '44' Undefined number format id '42' Unexpected element 'workbookProtection' in state : workbook – Bruni Jun 01 '18 at 15:07
  • It worked with the -S flag on the ods though, so +1 – Bruni Jun 01 '18 at 15:09
  • @Bruni, help me: could you please fix my incomplete answer? `ssconvert -O 'separator=:: format=raw quoting-mode=never' x.xlsx -S 'out-%n.txt'` ? –  Jun 01 '18 at 15:12
  • It works the way I modified your answer, though the naming schmeme of the result is suboptimal – Bruni Jun 01 '18 at 15:15
  • @Bruni, if the output name is ` 'out-%n.txt'` or `'out-%n.txt'` it should include number or name of the sheet. –  Jun 01 '18 at 15:18
  • You are right. As I modified it now it works – Bruni Jun 01 '18 at 15:26