1

Does function CSV->PRINT only accept array reference? I am pushing $cell2_value in an array and then printing the array (i.e rows), it would be nice if I can directly write $cell2_value into an opened CSV file.

Things to take care of -

  1. Excel cell having commas in their value would print in double-quotes.
  2. Excel cells having "keyword" in their value would print the whole-cell value in double-quotes and inner quotes will change to ""keyword"".

  I can write a CSV file with a few undesired outputs of excel cells. its inserting double quotes whenever it sees special characters like / or *.

CSV FILE from the below code:-
"CLASS_A,,x,Singapore,,0xABCF00C4,"/* x2-4Rw */",-,,,,,,,Yes,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"CHECK- ""CLASS_B""- WORKED",1,2,3"
"CLASS_A,,,malyaisa,," 3:0","/* ABCVF */",E,,,,,,,Yes,,,,,,,,,,,,,Yes,,,,,,Yes,Yes,Yes,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"

Desired output
CLASS_A,,x,malyaisa,,0xABCF00C4,/* x2-4Rw */,-,,,,,,,Yes,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"CHECK- ""CLASS_B""- WORKED",1,2,3
CLASS_A,,,malyaisa,, 3:0,/* ABCVF */,E,,,,,,,Yes,,,,,,,,,,,,,Yes,,,,,,Yes,Yes,Yes,,,,,,,,,,,,,,,,,,,

Why it's inserting quotes around each line? Is there any way to remove it?

sub Excel_to_CSV 
{
($student_excel_file) = @_;

if($student_excel_file ne "")
{
   $student_excel_out_csv_file = $student_excel_file;
   $student_excel_out_csv_file =~ s/.xlsm$/_new.csv/;
   my $parser_1   = Spreadsheet::ParseXLSX->new();
   my $workbook = $parser_1->parse($student_excel_file);
   my $csv_1 = Text::CSV->new ({ binary => 1, auto_diag => 1, sep_char  => ',' });
   open my $fh, ">:encoding(utf-8)", $student_excel_out_csv_file or die "failed to create $student_excel_out_csv_file: $!";
   if ( !defined $workbook ) 
   {
      die $parser_1->error(), ".\n";
   }
   my $worksheet=$workbook_->worksheet(0);
   my ( $row_min, $row_max ) = $worksheet->row_range();
   my ( $col_min, $col_max ) = $worksheet->col_range();
   printf("Copyig Sheet: %s from the provided student \n", $worksheet->{Name});
   my $concurentEmptyLineCount = 0;
   for my $row_1 ( $row_min .. $row_max ) 
   {
      my @row_elements_array;
      for my $col_1 ( $col_min .. $col_max ) 
         {
            my $cell_1 = $worksheet->get_cell( $row_1, 0 );
            next unless $cell_1;
            $concurentEmptyLineCount=0;
            my $cell_2 = $worksheet->get_cell( $row_1, $col_1);
            my $cell2_value =$cell_2 -> {Val};
            if(defined $cell2_value)
            {
               push(@row_elements_array, $cell2_value);
               
            }
            else
            { 
               my $blank="";    
               push(@row_elements_array, $blank);
               }
            }

            my $next_line="\n";
            push(@row_elements_array, $next_line);
                
            my @temp_row_elements_array= @row_elements_array;
            $csv_1->print($fh, \@temp_row_elements_array);
    
    
    }
close $fh;
}
return $student_excel_out_csv_file;
}
himmat
  • 159
  • 5
  • 1
    If I try `$csv->say(\*STDOUT, ['foo','/* x2-4Rw */','bar']);` I get output: `foo,"/* x2-4Rw */",bar` . So it is only quoting the middle field, not the whole line – Håkon Hægland Jun 25 '20 at 05:45
  • @himmat please check this https://metacpan.org/pod/Text::CSV#combine and https://stackoverflow.com/questions/62523724/how-to-automatically-change-the-format-or-encode-the-excel-cell-value-while-writ urls – amit bhosale Jun 26 '20 at 18:46

1 Answers1

-1

Based on my understanding of your requirements, one solution is to remove the extraneous double-quotes from $cell2_value before pushing it into @row_elements_array. For example:

$cell2_value =~ s/"(")*/$1/g; 
push(@row_elements_array, $cell2_value);
LeadingEdger
  • 604
  • 4
  • 7