in reply to XLS to CSV file with Text wrap

When writing your CSV, you should use Text::CSV_XS instead of using print to write the data.

My approach to writing a CSV file from data would be to convert the Excel data to an array of arrays and then write that out through the csv function of Text::CSV_XS. See the reply by Tux for better code, but this is the basic idea:

...
my $sheet = [];
foreach my $row_index ($source_sheet->row_range) {
  my $row = [];
  foreach my $col_index ($source_sheet->col_range) {
   my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
   if ($source_cell)
   {
    #print "( $row_index , $col_index ) =>", $source_cell->Value, ",";
    push @$row, $source_cell->Value;
   }
  } 
  push @$sheet, $row;
}

csv( in => $sheet, out => 'file.csv' );

[download]

See the documentation of Text::CSV_XS on the csv function.

Replies are listed 'Best First'.
Re^2: XLS to CSV file with Text wrap
by kshitij (Sexton) on Sep 01, 2018 at 04:27 UTC
    HI Corion ,

    I am using below code as per your inputs but getting an error.

    Error 

    
-------- SHEET:Projects
Undefined subroutine &main::csv called at XLS2CSV.pl line 40.

    [download]
    My script 
    
#!/usr/bin/perl -w
#use Text::CSV_XS;
use strict;
use Spreadsheet::ParseExcel;

use Text::CSV_XS;
my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n", auto_diag =
+> 1 });
open my $fh, ">", "file.csv" or die "file.csv: $!";
my $sourcename = shift @ARGV or die "invocation: $0 <source file>\n";
my $source_excel = new Spreadsheet::ParseExcel;
my $source_book = $source_excel->Parse($sourcename) or die "Could not 
+open source Excel file $sourcename: $!";
my $storage_book;

foreach my $source_sheet_number (0 .. $source_book->{SheetCount}-1)
{
 my $source_sheet = $source_book->{Worksheet}[$source_sheet_number];

 print "--------- SHEET:", $source_sheet->{Name}, "\n";

 next unless defined $source_sheet->{MaxRow};
 next unless $source_sheet->{MinRow} <= $source_sheet->{MaxRow};
 next unless defined $source_sheet->{MaxCol};
 next unless $source_sheet->{MinCol} <= $source_sheet->{MaxCol};
  my @sheet = ();
 foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{Max
+Row})
 {
  my @row= ();
  foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{Ma
+xCol})
  {
   my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
 if ($source_cell)
   {
push @row, $source_cell->Value;    
#print "( $row_index , $col_index ) =>", $source_cell->Value, ",";
   # print  $source_cell->Value, ",";
   }
}
push @sheet, @row;
  }
csv-> (in => @sheet, out => 'file.csv'); 
  #print  "\n";
  

}

    [download]
    Could you provide your valuable inputs ?
[reply]
[d/l]
[select]

      You need to import the csv subroutine from Text::CSV_XS, just like the documentation says.

[reply]
[d/l]

        Hi Corion I am using the below code and calling the subroutine by specifying below stated property use Text::CSV_XS qw( csv ) but still I am getting same error Please find my script 

        
#!/usr/bin/perl -w
#use Text::CSV_XS;
use strict;
use Spreadsheet::ParseExcel;
use Text::Wrap;
#use Text::CSV_XS;
use Text::CSV_XS qw( csv );
#my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\r\n", auto_diag 
+=> 1 });
#open my $fh, ">", "file.csv" or die "file.csv: $!";
my $sourcename = shift @ARGV or die "invocation: $0 <source file>\n";
my $source_excel = new Spreadsheet::ParseExcel;
my $source_book = $source_excel->Parse($sourcename) or die "Could not 
+open source Excel file $sourcename: $!";
my $storage_book;
my $sheet = [];
foreach my $source_sheet_number (0 .. $source_book->{SheetCount}-1)

#use Text::CSV_XS qw( csv );

{
#

 my $source_sheet = $source_book->{Worksheet}[$source_sheet_number];

 print "--------- SHEET:", $source_sheet->{Name}, "\n";

 next unless defined $source_sheet->{MaxRow};
 next unless $source_sheet->{MinRow} <= $source_sheet->{MaxRow};
 next unless defined $source_sheet->{MaxCol};
 next unless $source_sheet->{MinCol} <= $source_sheet->{MaxCol};
 # my $sheet = [];
 foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{Max
+Row})
 {
  my $row= [];
  foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{Ma
+xCol})
  {
   my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
 if ($source_cell)
   {
push @$row, $source_cell->value;    
#print "( $row_index , $col_index ) =>", $source_cell->Value, ",";
   # print  $source_cell->Value, ",";
   }
}
push @$sheet, $row;
  }
use Text::CSV_XS qw( csv );

csv(in => $sheet, out => 'file.csv'); 

}

        [download]

        Error: experiment2 % perl XLS2CSV.pl Project_status_tracking_DFT.xls --------- SHEET:Projects Undefined subroutine &main::csv called at XLS2CSV.pl line 48.

        Could you help me out by providing your valuable inputs ? Thanks Kshitij
[reply]
[d/l]

In Section Seekers of Perl Wisdom