Centos – How to do a fast convert of a .xls (MS Excel) file to .csv

centoscsvjavascriptlinuxPHP

How can I do a fast convert of excel to csv? I have tried with php, perl, JavaScript. but it takes too much time. For example, for a 5MB file it will take 3-5 minutes.

Best Answer

libreoffice offers this option, I cannot tell about resource use, though:

libreoffice --headless --convert-to csv $filename(s) --outdir $outdir

--outdir is optional and will be the current working dir if not specified, batch mode is possible by simply specifying multiple files.

Related Solutions

Conversion – Convert .xlsx to .csv with Semicolon Separated Fields

OpenOffice comes with the unoconv program to perform format conversions on the command line.

unoconv -f csv filename.xlsx

For more complex requirements, you can parse XLSX files with Spreadsheet::XLSX in Perl or openpyxl in Python. For example, here's a quickie script to print out a worksheet as a semicolon-separated CSV file (warning: untested, typed directly in the browser):

perl -MSpreadsheet::XLSX -e '
    $\ = "\n"; $, = ";";
    my $workbook = Spreadsheet::XLSX->new()->parse($ARGV[0]);
    my $worksheet = ($workbook->worksheets())[0];
    my ($row_min, $row_max) = $worksheet->row_range();
    my ($col_min, $col_max) = $worksheet->col_range();
    for my $row ($row_min..$row_max) {
        print map {$worksheet->get_cell($row,$_)->value()} ($col_min..$col_max);
    }
' filename.xlsx >filename.csv

Linux Bash Shell Script AWK Perl – How to Convert CSV to XLS File on Linux

For automatically converting CSV files to XLS/XLSX ones you can also use ssconvert (which comes with Gnumeric) or unoconv (which uses LibreOffice).

SSConvert Example

$ echo -e 'surname,name,age\nCarlo,Smith,23\nJohn,Doe,46\nJane,Doe,69\nSarah,Meyer,23\n' \
     > example.csv
$ unix2dos example.csv
$ ssconvert example.csv example.xlsx
$ ssconvert example.csv example.xls

Where the first ssconvert call creates a MS Excel 2007/2010 file and the second an old school Excel 2007 one.

You can check the files via file:

$ file example.csv
example.csv: ASCII text, with CRLF line terminators
$ file example.xls
example.xls: Composite Document File V2 Document, Little Endian, Os: Windows, Version 4.10,
   Code page: 1252, Create Time/Date: Tue Sep 30 20:23:18 2014
$ file example.xlsx 
example.xlsx: Microsoft Excel 2007+

You can list all supported output file formats via:

$ ssconvert --list-exporters
ID                           | Description
[..]
Gnumeric_Excel:xlsx2         | ISO/IEC 29500:2008 & ECMA 376 2nd edition (2008);
                               [MS Excel™ 2010]
Gnumeric_Excel:xlsx          | ECMA 376 1st edition (2006); [MS Excel™ 2007]
Gnumeric_Excel:excel_dsf     | MS Excel™ 97/2000/XP & 5.0/95
Gnumeric_Excel:excel_biff7   | MS Excel™ 5.0/95
Gnumeric_Excel:excel_biff8   | MS Excel™ 97/2000/XP
[..]

Unoconv Example

$ unoconv --format  xls example.csv

which creates example.xls, which is a Excel 97/2000/XP file.

Check via file:

$ file example.xls 
example.xls: Composite Document File V2 Document, Little Endian, Os: Windows, Version 1.0,
  Code page: -535, Revision Number: 0

You can list all supported file formats via:

$ unoconv --show
[..]
The following list of spreadsheet formats are currently available:

  csv      - Text CSV [.csv]
  dbf      - dBASE [.dbf]
[..]
  ooxml    - Microsoft Excel 2003 XML [.xml]
[..]
  xls      - Microsoft Excel 97/2000/XP [.xls]
  xls5     - Microsoft Excel 5.0 [.xls]
  xls95    - Microsoft Excel 95 [.xls]
[..]