Convert a .xlsx (MS Excel) file to .csv on command line with semicolon separated fields

Question

I realize that this is not an entirely unix/linux related question. But since this is something I'll do on linux, I hope someone has an answer.

I have an online excel file (.xlsx) which gets updated periodically (by someone else). I want to write a script and put it in as a cronjob in order to to process that excel sheet. But to do that, I need to convert that into a text file (so a .csv) with semicolon separated columns. It can't be comma separated unfortunately since some columns have commas in them. Is it at all possible to do this conversion from shell? I have Open office installed and I can do this by using its GUI, but want to know if it is possible to do this from command line. Thanks!

PS: I have a Mac machine as well, so if some solution can work there, thats good as well. :)

Answer 1

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

Answer 2

https://github.com/dilshod/xlsx2csv

Worked well for me. About 85 MB XLSX file converted at about 3 minutes on a Mac Book Pro SSD.

Answer 3

I'm using Perl's xls2csv to convert xls files to csv.

Not sure tho if it works with xlsx too.

About:

It can't be comma separated unfortunately since some columns have commas in them

that's why quoting has been introduced:

1,2,"data,data, more data"

Answer 4

I use PHP. Just instal the PHPExel library from http://phpexcel.codeplex.com/ and probably you need XML functions too.

This is my code :

<?php

error_reporting(E_ALL);
date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */

require_once '/home/markov/Downloads/1.7.6/Classes/PHPExcel/IOFactory.php';

$file="RIF394305.xlsx"; //PATH TO CSV FILE

// Check prerequisites

if (!file_exists($file)) {
    exit("Please run 06largescale.php first.\n");
}

$objReader = PHPExcel_IOFactory::createReader('Excel2003XML');

$objPHPExcel = $objReader->load($file);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV');

$objWriter->save(str_replace('.xlsx', '.csv',$file));
?>

You can revert the process or use different Excel/CSV format. Look at the different php files in the PHPExcel directory.