CODEWITHSUNDEEP
perl
john roberto
john roberto

Reputation: 45

Multiple XLSX files to multiple CSV files using perl

I have the below script which is converting xlsx to csv but if the cell value has comma (,) in between, it is moving to next column in csv which is wrong. Colud you please correct it? Also, how can convert multiple xlsx files to mulptiple csv files in one go?

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::XLSX;
use Text::CSV qw(csv);

my $excel = Spreadsheet::XLSX -> new ('/path/file.xlsx');
my $csv = '/path/File.csv';
open(my $FH ,'>',"$csv") or die "failed to open";

my $line;
foreach my $sheet (@{$excel -> {Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    $sheet -> {MaxRow} ||= $sheet -> {MinRow};
    foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
        $sheet -> {MaxCol} ||= $sheet -> {MinCol};
        foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {
            my $cell = $sheet -> {Cells} [$row] [$col];
            #if ($cell) {
            #    $line .= "\"".$cell -> {Val}."\",";
                        #       $line .= $cell -> {Val};
                        #       if ($col != $sheet -> {MaxCol}) #appends the comma only if the column being processed is not the last
                        #       {
                        #               $line .= ",";
                        #       }
            #}
                        if (defined $cell && defined $cell->Value) {
               if ($col != $sheet -> {MaxCol})
               {
               print $FH $cell->Value.",";
              }
            else
             {
            print $FH $cell->Value ;
             }
          } else {
            if ($col != $sheet -> {MaxCol})
               { print $FH ",";
               }
             }

        }
$FH =~ s/,$//; # replace comma at the end of the string with empt
       print $FH "\n";
      }

Upvotes: 1

Views: 318

Answers (2)

Snorik
Snorik

Reputation: 211

Regarding the multiple files question, you should be able to do something like this:

my @csv = ('/path/File.csv', 'secondfile', 'thirdfile');

foreach (@csv)
{
    my $excel = Spreadsheet::XLSX -> new ($_.".xslx");
    ...
}

Upvotes: 1

amit bhosale
amit bhosale

Reputation: 482

Check for the cell value contain ',' char. if ',' char present in sting add double quotes to string. Write a method and pass $cell->value to check string contain char ','.

for example

sub check_cell_string {     
    my ($string) = @_;     
    my $substr = ',';     
    if (index($string, $substr) != -1) { 
        $string = '"'.$string.'"';
    }     
    return $string; 
}

and then call file write statement.

my $str = check_cell_string($cell->value);
print $FH $str;

for example, in the csv file entry look like below

1, 1928,44,Emil Jannings,"The Last Command, The Way of All Flesh"

Upvotes: 3

Related Questions