convert CSV to XLS file on linux

Question

The following Perl script can convert CSV file to XLS file

the problem is that I need to install on customer Linux machine many Perl modules

in order to run this Perl script , and actually I cant because the Linux machine is customer machine ( not allow to install modules )

So I need to find some other alternative for this Perl script

First customer have Linux red-hat machine version 5.X

And I want to find some bash/ksh/sh/awk scripts that can do the job the same as the perl script do

so I want to find other alternative that convert CSV to XLS file

Please advice how to find this script? or other advice to convert CSV to XLS on Linux machine

#!/usr/bin/perl -w

###############################################################################
#
# Example of how to use the WriteExcel module
#
# Simple program to convert a CSV comma-separated value file to an Excel file.
# This is more or less an non-op since Excel can read CSV files.
# The program uses Text::CSV_XS to parse the CSV.
#
# Usage: csv2xls.pl file.csv newfile.xls
#
#
# NOTE: This is only a simple conversion utility for illustrative purposes.
# For converting a CSV or Tab separated or any other type of delimited
# text file to Excel I recommend the more rigorous csv2xls program that is
# part of H.Merijn Brand's Text::CSV_XS module distro.
#
# See the examples/csv2xls link here:
#     L<http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>
#
# reverse('©'), March 2001, John McNamara, [email protected]
#

use strict;
use Spreadsheet::WriteExcel;
use Text::CSV_XS;

# Check for valid number of arguments
if ( ( $#ARGV < 1 ) || ( $#ARGV > 2 ) ) {
    die("Usage: csv2xls csvfile.txt newfile.xls\n");
}

# Open the Comma Separated Variable file
open( CSVFILE, $ARGV[0] ) or die "$ARGV[0]: $!";

# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel->new( $ARGV[1] );
my $worksheet = $workbook->add_worksheet();

# Create a new CSV parsing object
my $csv = Text::CSV_XS->new;

# Row and column are zero indexed
my $row = 0;

while (<CSVFILE>) {
    if ( $csv->parse($_) ) {
        my @Fld = $csv->fields;

        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write( $row, $col, $token );
            $col++;
        }
        $row++;
    } else {
        my $err = $csv->error_input;
        print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
    }
}

Solution

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]
[..]

Comments

[Ramesh]

I am so tempted to give this answer. cheat: rename file.csv to file.xls. excel opens it and you never notice the difference.

[Angel Todorov]

question also asked at stackoverflow.com/q/26101554/7552

[maihabunash]

yes but no answer from stackoverflow so maybe here I will get nice answer -:)

[Ramesh]

@maihabunash, you should either delete the question posted in stack overflow or here. If you have it posted in 2 places, it will get closed.

[Anthon]

CSV files (in general) are difficult to parse, you have cell values with newlines, quotes and commas. XLS files (the old ones, not the more modern XLSM xml files) are binary files that are non-trivial to generate.

[Jeff Schaller]

SO post was closed at some point. Reopening.

[maihabunash]

please advice about the syntax how the syntax to convert csv to xls?

[maxschlepzig]

@maihabunash, I've added some examples.

[zingo]

This: soffice --convert-to xlsx:"Calc MS Excel 2007 XML" filename.csv --headless is quite useful as well.

[Curious]

Please add options to merge csv to single xls as well. Could not find in any man page for Unoconv or SSconvert