Perl convert csv to xls

hawk__0 · 09-15-2009, 01:52 PM

I am not good at perl, in fact, I hadrly understand it. I found this script on the internet that will convert csv to xls:

Code:

#!/usr/bin/perl
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->add_worksheet();
open(FH,"<file1.csv") or die "Cannot open file: $!\n";
my ($x,$y) = (0,0);
while (<FH>){
 chomp;
 @list = split /\s+/,$_;
 foreach my $c (@list){
    $worksheet->write($x, $y++, $c);
 }
 $x++;$y=0;
}
close(FH);
$workbook->close();

It works great, aside from the problem with how it chooses to make it space delimited. Is there a way to make it comma delimited?

Also, is it possible to change it so that "file1.csv" is a command line argument?

Thanks!

nadroj · 09-15-2009, 05:27 PM

Quote:

Originally Posted by hawk__0

Code:

@list = split /\s+/,$_;

it is this call to the perl function "split" that makes it "whitespace delimited". this line is saying "take the variable named $_, split it into an array where each one or more 'whitespaces' means a new element in this array, and store the resulting array into the variable named @list". the delimiter is "\s+" which says "one or more (white)spaces". to make it seem comma delimited change "\s+" to ","

edit:
you will see weird behaviour when values in the input CSV file contain your delimiter, ",". if the input file looks like:

Code:

header1,header2,header3
value1,"value2,",value3

then the "split" function will return the the following array (i will put each item on its own line):

Code:

header1
header2
header3
value1
"value2
"
value3

because of this you will have to make it "smarter" and do some checks/string manipulation on the input. one example may be to change the regular expression that your passing to "split". note: the current script will have problems if the values contain the delimiter it is using, which is whitespace, so this problem isnt because your changing it to comma delimited, but because the script initially doesnt handle these cases (which may be very likely to occur).

Sergei Steshenko · 09-15-2009, 07:48 PM

Quote:

Originally Posted by hawk__0

I am not good at perl, in fact, I hadrly understand it. I found this script on the internet that will convert csv to xls:

Code:

#!/usr/bin/perl
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->add_worksheet();
open(FH,"<file1.csv") or die "Cannot open file: $!\n";
my ($x,$y) = (0,0);
while (<FH>){
 chomp;
 @list = split /\s+/,$_;
 foreach my $c (@list){
    $worksheet->write($x, $y++, $c);
 }
 $x++;$y=0;
}
close(FH);
$workbook->close();

It works great, aside from the problem with how it chooses to make it space delimited. Is there a way to make it comma delimited?

Also, is it possible to change it so that "file1.csv" is a command line argument?

Thanks!

From your questions it appears you've never learned Perl. Is my guess correct ?

If yes, why not to invest some effort before asking ?

theNbomr · 09-16-2009, 09:13 AM

Just as there is a (presumably) good module for creating Excel spreadsheets, there is also a number of good modules for parsing CSV files, which, as nadroj points out, requires more effort than simple string splitting. Go to cpan.org, and search the modules list for 'CSV'. Many hits will emerge. Parse::CSV and Text::CSV seem like decent first candidates.
--- rod.