Convert xlsx to csv in Linux with command line

Asked 11/5, 2012 at 19:18 Answered 1/4, 2022 at 12:33

384

I'm looking for a way to convert xlsx files to csv files on Linux.

I do not want to use PHP/Perl or anything like that since I'm looking at processing several millions of lines, so I need something quick. I found a program on the Ubuntu repos called xls2csv but it will only convert xls (Office 2003) files (which I'm currently using) but I need support for the newer Excel files.

Any ideas?

Rrhoea answered 11/5, 2012 at 19:18 Comment(8)

Thinking that anything implemented with a scripting language is going to be slow by nature seems... a little misguided, particularly since the interesting libraries in those languages tend to have backends written in C. – Canadianism 11/5, 2012 at 19:34

Excel used to be limited to 65536 rows. Now it's 1,048,576 (support.microsoft.com/kb/120596). it's going to be tough to fit "sever millions of lines" in it. just saying... – Tele 11/5, 2012 at 19:35

@Pavel could be over several files. – Canadianism 11/5, 2012 at 19:38

...personally, I'd do this using the xlsv library for Python, but since scripting-based approaches are described as out of the question... shrug. (How is it a programming question if programmatic tools are excluded from the answer?) – Canadianism 11/5, 2012 at 19:39

@CharlesDuffy I'm currently using a PHP library to do this, and what takes xls2csv 1 second to do, takes php 10 minutes to do. Literally. – Rrhoea 11/5, 2012 at 19:41

@Rrhoea I believe it -- PHP is a raving pile of... well. But just because you have a really bad PHP library isn't a valid reason to write off all interpreted languages. – Canadianism 11/5, 2012 at 20:7

(err, that library name should have been xslw, not xlsv) – Canadianism 11/5, 2012 at 20:8

Related: unix.stackexchange.com/questions/23726/… – Beggarly 14/12, 2020 at 10:0

345

The Gnumeric spreadsheet application comes with a command line utility called ssconvert that can convert between a variety of spreadsheet formats:

$ ssconvert Book1.xlsx newfile.csv

Using exporter Gnumeric_stf:stf_csv

$ cat newfile.csv

Foo,Bar,Baz
1,2,3
123.6,7.89,
2012/05/14,,
The,last,Line

To install on Ubuntu:

apt-get install gnumeric

To install on Mac:

brew install gnumeric

Hyssop answered 14/5, 2012 at 9:34 Comment(16)

Really the most hassle-free method of converting spreadsheets. Combined with a bash script, it will let you batch-process multiple files. for f in *.csv; do ssconvert "$f" "${f%.csv}.xlsx"; done The LibreOffice method could probably process other formats, but I could not make it work (it would simply open a blank file every time, even with the --headless argument). – Ackerley 15/8, 2013 at 17:24

@sebleblanc Not quite hassle-free. The installation is a pain given the number of dependencies (if you're doing this on a headless server). So far gcc, intltool, zlib-devel, GTK... GTK requires glib, atk, pango, cairo, cairo-object, gdk-pixbuf-2.0... – Pilloff 14/2, 2014 at 18:3

It seems that there is now way to prevent ssconvert to recompute the sheet before converting it to csv... See #22345418 – Defensible 12/3, 2014 at 10:43

I managed to install it on a headless debian server with apt-get install gnumeric --no-install-recommends. The only drawback is that it fires lots of warnings GConf-WARNING **: Client failed to connect to the D-BUS daemon when running. A simple ssconvert oldfile.xlsx newfile.csv > /dev/null 2>&1 will do the trick. – Jobless 18/3, 2014 at 11:5

im not finding ssconvert for cygwin: screencast.com/t/4GgSOJNLa screencast.com/t/seIb1GHKOI – Jeddy 23/3, 2014 at 4:34

i also tried installing gnumeric on windows and still nothing screencast.com/t/4QcjZVXHoh ... its a joke i guess? – Jeddy 23/3, 2014 at 4:40

@Yuck - Not sure of what your screen-shot wants to illustrate (it only shows that you don't have the binary in your cygwin PATH) but I've just tried native gnumeric-1.12.17-20140610.exe (no cygwin) and it works flawlessly. – Munster 19/8, 2014 at 10:45

To write to csv you may want the -S flag to write multiple sheets. Each goes to its own file. – Pollitt 25/2, 2015 at 15:37

I've tried using this in Ubuntu 12.04 but get the following error: {ssconvert test.xlsb newfile.csv Using exporter Gnumeric_stf:stf_csv ** (ssconvert:4973): WARNING **: Format Gnumeric_Excel:xlsx's probe changed input ref_count from 1 to 3. ** (ssconvert:4973): WARNING **: Format Gnumeric_Excel:xlsx's probe changed input ref_count from 3 to 5. E Unsupported file format.} Any ideas? – Parnassus 6/12, 2015 at 20:36

How can you specify the separator? $ ssconvert -O 'separator=;' file.csv file.xlsx or $ ssconvert -O 'separator=; format=raw' file.csv file.xlsx do not work. – Brumby 26/1, 2017 at 10:35

@Brumby The separator option only works with txt export type. You can use this to print to stdout: ssconvert -O "separator=;" -T Gnumeric_stf:stf_assistant file.xlsx fd://1. – Carver 5/9, 2017 at 10:52

We should probably accept this answer as the recommended solution. – Unusual 19/8, 2019 at 2:29

Under macOS I had to use like this: ssconvert --export-type=Gnumeric_stf:stf_csv Book1.xlsx newfile.csv – Kephart 21/4, 2020 at 14:3

The bash script of @Ackerley works fine, but does the inverse of what is asked for. – Dangerous 17/6, 2022 at 8:15

@Dangerous oopsies! – Ackerley 18/6, 2022 at 14:34

oh i just wished ssconvert was installable as a separate app! I was just trying to do this on wsl2 debian, and i cant do this, dont want all those gnome dependencies to install for my little work. A little python script will be best for this. – Destitution 12/8, 2022 at 6:5

187

If you already have a desktop environment then I'm sure Gnumeric or LibreOffice would work well, but on a headless server (e.g. any cloud-based environment), they require dozens of dependencies that you also need to install.

I found this Python alternative: xlsx2csv

easy_install xlsx2csv
xlsx2csv file.xlsx > newfile.csv

It took two seconds to install and works like a charm.

If you have multiple sheets, you can export all at once, or one at a time:

xlsx2csv file.xlsx --all > all.csv
xlsx2csv file.xlsx --all -p '' > all-no-delimiter.csv
xlsx2csv file.xlsx -s 1 > sheet1.csv

He also links to several alternatives built in Bash, Python, Ruby, and Java.

Pilloff answered 14/2, 2014 at 18:34 Comment(8)

Works great, but I can get to run only as sudo (

IOError: [Errno 13] Permission denied: '/usr/local/lib/python2.7/dist-packages/prettytable-0.7.2-py2.7.egg/EGG-INFO/top_level.txt'

). Now that I think about it, I got the same error with csvkit. – Arris 28/5, 2014 at 17:24

....Was working great for me and allowing the extraction of each sheet to individual files using the -s option -- where libreoffice was not able to handle the size of the sheet, xlsx2csv had no problems – Silsby 29/5, 2014 at 18:20

Thanks! Very convenient in ubuntu. – Gawky 16/11, 2015 at 7:1

In Debian and Ubuntu there is the xlsx2csv package, so you don't need to manually install it through easy_install but can use your package manager. – Tillie 4/5, 2016 at 6:4

On MacOS you will need a sudo easy_install xlsx2csv – Melise 7/1, 2020 at 17:16

seems there is no brew formula for this, so I used csvkit from the next answer – Redeemable 28/11, 2022 at 4:28

xls2csv gave mixxed up a lot of column values in my case.. – Ulaulah 11/1, 2023 at 15:32

I have no idea how robust or feature-complete xlsx2csv is but it seems to be actively maintained and compared to installing Gnumeric on macOS via Homebrew which involves more than 30 dependencies and LibreOffice which is a several hundred MB download xlsx2csv has zero(!) dependencies, comes at just 50 KB and worked perfectly for my use case (converting the output of PaddleOCR to csv). Either install it with pip install xlsx2csv or download the latest release from the Repository and run xlsx2csv.py. – Shishko 12/4, 2023 at 22:51

183

You can do this with LibreOffice:

libreoffice --headless --convert-to csv $filename --outdir $outdir

For reasons not clear to me, you might need to run this with sudo. You can make LibreOffice work with sudo without requiring a password by adding this line to you sudoers file:

users ALL=(ALL) NOPASSWD: libreoffice

Tinct answered 13/2, 2013 at 14:54 Comment(15)

how would I tell libreoffice that I want the second sheet? – Rene 8/5, 2013 at 7:30

Allowing sudo to libreoffice for everyone without password is opening a can of worms. Please beware of the consequences, including the possibility to acquiring root permissions on a multi-user platform – Susysuter 1/8, 2013 at 7:42

this worked for me (sudo not required). My version: libreoffice-calc-3.6.7.2-4.fc18.x86_64 – Gangue 8/1, 2014 at 16:32

I just tried and first I needed sudo. Then I is because my current user had an instance of libreoffice running (playing an ods). After closing it, no sudo required. – Faso 6/2, 2014 at 21:48

/Applications/LibreOffice.app/Contents/MacOS/soffice --headless --convert-to csv $filename worked on OS X for me. – Luhey 10/6, 2014 at 20:52

This method is good but AFAIK it will break non-ascii characters – Hendecasyllable 13/8, 2014 at 8:46

Make sure all libreoffice instances are closed otherwise it won't work. – Postfree 31/12, 2014 at 11:10

libreoffice --convert-to works well to convert between one spreadsheet format and another (I use it to read .xlsb files, by converting them to .xls first). But for writing to CSV, it is limited to outputting the first sheet only. – Pollitt 25/2, 2015 at 14:1

To convert to utf-8, preserving non-ascii characters, use instead --convert-to "csv:Text - txt - csv (StarCalc):44,34,76,1,1/1". See open office wiki for details. – Perforate 14/7, 2015 at 17:30

How do I read multiple sheets? I.e. i want to read sheet 7 only and create a .csv. – Parnassus 6/12, 2015 at 20:44

@Rene you don't. You use another tool like xlsx2csv if you need that. The xlsx2csv tool has the -s or --sheet option which you can use to select the sheet (0 stands for "all sheets" and the default is 1). xlsx2csv is packaged in popular Linux distributions like Debian, Ubuntu and Arch Linux. – Tillie 4/5, 2016 at 6:1

@dmeu: use this script. Works like a charm: ./libreconverter.py Spreadsheet.xls:"Sheet Name" output.csv. – Maidy 16/12, 2016 at 10:24

I had encoding problems in the csv after using this answer, the ssconvert answer solved the issue. – Raconteur 14/3, 2017 at 13:10

There is no reason for libreoffice to require root shell, and it could be easily debugged, where it fails with a user account. – Soundless 13/5, 2019 at 12:59

I would be inclined to remove the "sudo" part from the answer, since it's really misleading. Probably the author installed LibreOffice using some uncommon ways, or was trying to write in an unwritable destination directory. But there is really no need to use sudo at all (yes, I've also tested that in a random GNU+Linux distribution). – Galop 13/9, 2023 at 10:34

Use csvkit:

in2csv data.xlsx > data.csv

For details, check their excellent documentation.

Euromarket answered 6/11, 2014 at 9:10 Comment(2)

this should be the top answer – Insubordinate 12/10, 2022 at 21:37

brew install csvkit. And done. – Redeemable 28/11, 2022 at 4:27

In Bash, I used this LibreOffice command (executable libreoffice) to convert all my .xlsx files in the current directory:

for i  in *.xlsx; do  libreoffice --headless --convert-to csv "$i" ; done

Close all your LibreOffice open instances before executing, or it will fail silently.

The command takes care of spaces in the filename.

I tried it again some years later, and it didn't work. This question gives some tips, but the quickest solution was to run as root (or running a sudo libreoffice). It is not elegant, but quick.

Use the command scalc.exe in Windows.

Kike answered 8/2, 2014 at 20:54 Comment(4)

Make sure you close all openoffice windows before attempting this, as it will silently fail otherwise. – Hensley 4/8, 2014 at 8:53

Also, on Windows, the command is scalc.exe rather than libreoffice. Worked for me today on current stable LO version. – Redemption 24/4, 2017 at 18:38

fwiw, this worked for me right now, on ubuntu – Acquaint 2/6, 2021 at 4:35

Caution: Export only the first page. – Geneviegenevieve 4/7, 2021 at 17:59

Another option would be to use R via a small Bash wrapper for convenience:

xlsx2txt(){
echo '
require(xlsx)
write.table(read.xlsx2(commandArgs(TRUE)[1], 1), stdout(), quote=F, row.names=FALSE, col.names=T, sep="\t")
' | Rscript --vanilla - $1 2>/dev/null
}

xlsx2txt file.xlsx > file.txt

Euromarket answered 2/9, 2014 at 15:3 Comment(0)

If the .xlsx file has many sheets, the -s flag can be used to get the sheet you want. For example:

xlsx2csv "my_file.xlsx" -s 2 second_sheet.csv

second_sheet.csv would contain the data of the second sheet in my_file.xlsx.

Hydra answered 12/11, 2014 at 21:43 Comment(0)

Using the Gnumeric spreadsheet application which comes which a commandline utility called ssconvert is indeed super simple:

find . -name '*.xlsx' -exec ssconvert -T Gnumeric_stf:stf_csv {} \;

and you're done!

Originate answered 11/6, 2016 at 15:45 Comment(2)

Very useful and Thank you Mr.Pascal-Louis Perez – Nadabus 31/10, 2016 at 9:38

Above command 'ssconvert' only convert 65536 lines but I have more than one lacks lines, Can you able to help me? – Nadabus 7/11, 2016 at 5:22

You can use executable libreoffice to convert your .xlsx files to csv:

libreoffice --headless --convert-to csv ABC.xlsx

Argument --headless indicates that we don't need GUI.

Rainfall answered 30/12, 2021 at 4:17 Comment(1)

This answer was already given 8 years prior. – Colossal 25/3, 2022 at 11:18

If you are OK to run Java command line then you can do it with Apache POI HSSF's Excel Extractor. It has a main method that says to be the command line extractor. This one seems to just dump everything out. They point out to this example that converts to CSV. You would have to compile it before you can run it but it too has a main method so you should not have to do much coding per se to make it work.

Another option that might fly but will require some work on the other end is to make your Excel files come to you as Excel XML Data or XML Spreadsheet of whatever MS calls that format these days. It will open a whole new world of opportunities for you to slice and dice it the way you want.

Tele answered 11/5, 2012 at 19:42 Comment(1)

Do you know if this also supports .xlsx? – Unreadable 4/2, 2014 at 17:12

As others said, executable libreoffice can convert Excel files (.xls) files to CSV. The problem for me was the sheet selection.

This LibreOffice Python script does a fine job at converting a single sheet to CSV.

Usage is:

./libreconverter.py File.xls:"Sheet Name" output.csv

The only downside (on my end) is that --headless doesn't seem to work. I have a LibreOffice window that shows up for a second and then quits.

That's OK with me; it's the only tool that does the job rapidly.

Maidy answered 16/12, 2016 at 10:22 Comment(0)

You can use script getsheets.py. Add dependencies first:

pip3 install pandas xlrd openpyxl

Then call the script: python3 getsheets.py <file.xlsx>

Snifter answered 1/4, 2022 at 12:33 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags