Multiple XLSX files to multiple CSV files using perl

Question

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";
      }

still it is going to next column. Is there any other way to restrict the field with comma(,) in between to one field after conversion from xlsx to csv? — john roberto
– john roberto, Commented Jun 19, 2020 at 9:40
check for the cell value contain ',' if ',' present add double quotes. write method and pass $cell->value to check string contain ',' sub check_cell_string { my ($string) = @_; my $substr = ','; if (index($string, $substr) != -1) { $string = '"'.$string.'"';} return $string; } and before print $FH statement call this method my $str = check_cell_string($cell->value) ; print $FH $str.","; OR print $FH $str; — amit bhosale
– amit bhosale, Commented Jun 19, 2020 at 9:47
for example in csv file entry look like this 1, 1928,44,Emil Jannings,"The Last Command, The Way of All Flesh" — amit bhosale
– amit bhosale, Commented Jun 19, 2020 at 9:53
@briandfoy I have explained here .. I have deleted the script — amit bhosale
– amit bhosale, Commented Jun 19, 2020 at 11:51
@amit Your comment as it is could be an answer. If you have a lot of code in a comment, you're probably doing it wrong. — brian d foy
– brian d foy, Commented Jun 19, 2020 at 12:25

amit bhosale · Accepted Answer · 2020-06-23 07:35:31Z

3

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"

answered Jun 23, 2020 at 7:35

amit bhosale

4824 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

john roberto Over a year ago

Amit, another question for converting xlsx data to csv. In xlsx file, the data is like : Bob & Mathew but after conversion , the data in csv file is like: Bob &amp: Mathew. How to handle this? any idea?

amit bhosale Over a year ago

If you don't decode UTF-8 text/octets, Perl will assume they are encoded with N8CS (often ISO-8859-1/Latin-1). This means that the individual octets of a multi-byte UTF-8 character are seen as separate characters (not good). If these separate characters are later encoded to UTF-8 for output, a "double encoding" results. This is similar to HTML double encoding — e.g., &gt; instead of >.

amit bhosale Over a year ago

I am not aware of your current code but check this url stackoverflow.com/questions/21292229/… utf8::decode($str); # Fix double-encoding before writing to file decode string and url stackoverflow.com/questions/21096900/…

Snorik · Accepted Answer · 2020-06-23 08:21:58Z

1

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");
    ...
}

answered Jun 23, 2020 at 8:21

Snorik

2113 silver badges15 bronze badges

Collectives™ on Stack Overflow

Multiple XLSX files to multiple CSV files using perl

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related