Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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. :)

share|improve this question

4 Answers 4

up vote 12 down vote accepted

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
share|improve this answer
    
unoconv did not come with my OO, but I installed it and it works great (converts to comma separated file, not semicolon though)! Thanks! I will still need to figure out how I will get my fields that contain commas. But thanks anyways. –  allrite Nov 2 '11 at 19:35
    
@allrite Oh, I'd missed the requirement of semicolons as separators. My suggestion to do the processing in Python or Perl still stands. But I've also added a script (untested) to convert to CSV with ; as the separator. –  Gilles Nov 3 '11 at 0:27
    
Thanks! I used Spreadsheet::XLSX, but used the code in the CPAN link you provided. It works :) –  allrite Nov 7 '11 at 18:30
1  
An anonymous user had problems with this –  Michael Mrozek Mar 11 '12 at 5:41

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"
share|improve this answer
    
thanks for the tip, I will try that. I still prefer semicolon separated, since after the csv conversion, the file goes through awk scripts. And its just easier to pass semicolon as the field separator in awk. I could look for commas inside quotes to replace them with something else... now that's another question :) –  allrite Nov 2 '11 at 19:12
    
Thanks @neurino. I used Gilles method instead, but thanks for the reply anyways. –  allrite Nov 7 '11 at 18:31

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.