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

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

[download]

Replies are listed 'Best First'.
Re^6: Excel to CSV datetime conversion- format changes
by marto (Cardinal) on Apr 25, 2014 at 09:28 UTC

    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.

[reply]
[d/l]
[select]

      My source file has around 100 fields and I have problem while conversion with datetime format fields.

      changed the script to below code and got error as below in the output2.csv file:

      "Can't locate object method "new" via package "Spreadsheet::ParseExcel::Utility" at ./perl_excel_xls2n.pl line 15." 

      #!/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/etx/srcfile.xls";
my $e = new Spreadsheet::ParseExcel::Utility qw(ExcelFmt);
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

my $cell = $e->get_cell( $eSheet->{row}, $eSheet->{col} );
my $value = $cell->value();

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

      [download]
[reply]
[d/l]

        Spreadsheet::ParseExcel::Utility is not the same thing as Spreadsheet::ParseExcel, it's not a drop in replacement, it imports some extra functions, it's not a replacement for Spreadsheet::ParseExcel. Please read and understand the documentation of the modules you use within your programs.

        Update: For example:

        #!/usr/bin/perl -w

# For each tab (worksheet) in a file (workbook),
# spit out columns separated by ",",
# and rows separated by c/r.

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw(ExcelFmt);

# In your code:
# my $e = Spreadsheet::ParseExcel::Utility qw(ExcelFmt);
my $excel = Spreadsheet::ParseExcel->new();
# The rest of your script.....

        [download]
[reply]
[d/l]
Re^6: Excel to CSV datetime conversion- format changes
by poj (Abbot) on Apr 25, 2014 at 09:56 UTC
    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";
  }
}

    [download]
    poj
[reply]
[d/l]

      Thanks Poj. It helped me to see the all the date fields in a standard format(dd/mm/yyyy). I am happy for the output as of today. If anything I would require comeback to you. Thanks all of you for helping on this to get to this point. Thanks PERLMONKS!!

[reply]

In Section Seekers of Perl Wisdom