in reply to Re^2: Excel to CSV datetime conversion- format changes
in thread Excel to CSV datetime conversion- format changes

Thanks for the reply. I have seen ExcelFmt() function but I am not able to utilize in the current code. Can you please help me where to call that ExcelFmt in the line of code.

  • Comment on Re^3: Excel to CSV datetime conversion- format changes

Replies are listed 'Best First'.
Re^4: Excel to CSV datetime conversion- format changes
by runrig (Abbot) on Apr 24, 2014 at 17:04 UTC
    if ($something) { print ExcelFmt('dd/mm/yyyy hh:mm', $eSheet->{Cells}[$row][$column]-> +unformatted()) . "," } else { print $eSheet->{Cells}[$row][$column]->Value . ","; }
    You'll have to fill in the '$something' yourself, and I'm not absolutely sure about the format I've specified, and you may also need an 'elsif ($something_else)' block to print the format without the hours and minutes.

      I modified code as below and got error as

      Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 28. Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 28. Global symbol "$value" requires explicit package name at ./perl_excel_xls2n.pl line 30. Global symbol "$cell" requires explicit package name at ./perl_excel_xls2n.pl line 30. Execution of ./perl_excel_xls2n.pl aborted due to compilation errors.

      #!/usr/bin/perl -w # For each tab (worksheet) in a file (workbook), # spit out columns separated by ",", # and rows separated by c/r. use Spreadsheet::ParseExcel::Utility qw(ExcelFmt); use strict; my $file='output2.csv'; my $datefmt = 'dd/mm/yyyy'; # ISO 8601 open STDOUT, ">", $file or die "$0: open: $!"; open STDERR, ">&STDOUT" or die "$0: dup: $!"; my $filename = shift || "/home/etc/srcfile.xls"; my $e = new Spreadsheet::ParseExcel; my $eBook = $e->Parse($filename); my $sheets = $eBook->{SheetCount}; my ($eSheet, $sheetName); foreach my $sheet (0 .. $sheets - 1) { $eSheet = $eBook->{Worksheet}[$sheet]; $sheetName = $eSheet->{Name}; #print "#Worksheet $sheet: $sheetName\n"; next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{Max +Col}))); foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) { foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) { if (defined $cell->{Type} && $cell->{Type} eq 'Date') { $value = ExcelFmt($datefmt, $cell->{Val}); } if (defined $eSheet->{Cells}[$row][$column]) { print $eSheet->{Cells}[$row][$column]->Value . ","; } else { print ","; } } print "\n"; } }

        With use strict; you have to define variables before using them. That's what these mesages mean, you're using $cell and $value without having defined them.

        Try
        foreach my $sheet (0 .. $sheets - 1) { $eSheet = $eBook->{Worksheet}[$sheet]; $sheetName = $eSheet->{Name}; #print "#Worksheet $sheet: $sheetName\n"; next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCo +l}))); foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) { foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) { my $cell = $eSheet->{Cells}[$row][$column]; if (defined $cell){ if (defined $cell->{Type} && $cell->{Type} eq 'Date'){ print ExcelFmt($datefmt, $cell->unformatted() ) . ","; } else { print $cell->value() . ","; } } else { print ","; } } print "\n"; } }
        poj