Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: Perl to convert a named workseet from an xlsx workbook with multiple worksheets into a csv file

by Tux (Canon)
on Nov 22, 2014 at 09:36 UTC ( [id://1108088]=note: print w/replies, xml ) Need Help??


in reply to Perl to convert a named workseet from an xlsx workbook with multiple worksheets into a csv file

It is VERY error prone to use join "," to write CSV from cell data: what if the cell contains a , (or a newline)? Use a CSV module to generate CSV file, like Text::CSV_XS or Text::CSV.

If you use Spreadsheet::Read instead of your set of the three modules, you'd be able to pick a sheet by name. FWIW Spreadsheet::BasicRead requires the other two modules, but you do not have to include those requirements in your script).

For reading xlsx spreadsheets, please use Spreadsheet::ParseXLSX (Spreadsheet::BasicRead (still) uses Spreadsheet::XLSX which is buggy and unmaintained). Any alternative will make you suffer later on. (Spreadsheet::Read uses Spreadsheet::ParseXLSX).

Here is an example for using the modules (tested):

use 5.16.2; use warnings; sub usage { die "usage: $0 file.xlsx name [out.csv]\n"; } my $xls = shift or usage; my $sht = shift or usage; my $out = shift // $xls; $out =~ s/\.xlsx$/.csv/i; use Spreadsheet::Read; use Text::CSV_XS; my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n", auto_diag = +> 1 }); open my $fh, ">", $out or die "$out: $!\n"; my $book = ReadData ($xls) or die "$xls: $!\n"; my $sheet = $book->[$book->[0]{sheet}{$sht}]; foreach my $row (1 .. $sheet->{maxrow}) { $csv->print ($fh, [ Spreadsheet::Read::row ($sheet, $row) ]); } close $fh;

Enjoy, Have FUN! H.Merijn
  • Comment on Re: Perl to convert a named workseet from an xlsx workbook with multiple worksheets into a csv file
  • Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1108088]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (4)
As of 2024-04-26 04:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found