finhagen has asked for the wisdom of the Perl Monks concerning the following question:

I am attempting to convert a script I found on google to my purposes: (http://www.linuxjournal.com/content/reading-native-excel-files-perl). My script doesn't seem to actually read the spreadsheet I am specifying so I don't get any of my print statements after line 35. My output is as follows: 
Sinn:/lebensraum/perl # ./excel.syr.extract.pl \lebensraum\perl\SAMRep
+ort.xls 
Made it so far 
HASH(0xc682e0)

[download]
So my $worksheet variable prints as a HASH - which could be right but seems wrong to me. I have added a bunch of print statements in an effort to debug the problem, but most of those don't print. The DBI connection and insert statements work so I am not concerned about my mysql connection. My sense is the $workbook = Spreadsheet::ParseExcel::Workbook->Parse("\\lebensraum\\perl\\SAMReport.xls")or die "Unable to open $file\n"; statement is failing somehow but not in a way that reports an error. However, I am new to this module so I could easily be doing something else wrong. Any help would be greatly appreciated.
Hagen Finley
Boulder, CO
#!/usr/bin/perl

use DBI;
use DBD::mysql;
use CGI;
use Spreadsheet::ParseExcel;

# CONFIG VARIABLES
$platform = "mysql";
$database = "smdb";
$host = "Sinn";
$port = "3306";
$tablename = "site";
$user = "user";
$pw = "password";

#DATA SOURCE NAME
$dsn = "dbi:mysql:smdb:localhost:3306";

# PERL DBI CONNECT (RENAMED HANDLE)
$dbh = DBI->connect($dsn, $user, $pw)or die "Unable to connect: $DBI::
+errstr\n";

#$dbh->do("insert into site (siteid,name,city) values
#        (12345, \'BXB-200\',\'Boxborough\')");

#$cgi = new CGI;

$file = "\\lebensraum\\perl\\SAMReport.xls";

print "$file\n";

$workbook = Spreadsheet::ParseExcel::Workbook->Parse("\\lebensraum\\pe
+rl\\SAMReport.xls")or die "Unable to open $file\n";

print "Made it so far\n";
print "$workbook\n";

#locate columns in the spreadsheet from which we want to extract data
foreach $sheet (@{$workbook->{worksheet}}) {
print "Sheet number $sheet\n";

    foreach $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
        if ($sheet->{Cells}[0][$col]->{Val} eq "Site Number") {
            $siteid = $col;
            print "$siteid\n";}
        #else print "Could not find column Site Number\n";}

        if ($sheet->{Cells}[0][$col]->{Val} eq "Site Name") {
            $name = $col;
            print "$name\n";}
        #else print "Could not find column Site Name\n";}

        if ($sheet->{Cells}[0][$col]->{Val} eq "City") {
            $city = $col;
            print "$city\n";}
        #else print "Could not find column City\n";}
    
        
}

print"Column find completed successfully - moving on to row find\n";

#iterate through spreadsheet rows and extract site.siteid,site.name & 
+site.city

foreach $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {

    $site_number = $sheet->{Cells}[$row][$siteid]->{Val};
    $site_name = $sheet->{Cells}[$row][$name]->{Val};
    $site_city = $sheet->{Cells}[$row][$city]->{Val};

    print "$site_number\n";
    print "$site_name\n";
    print "$site_city\n";


    $dbh->do("insert into site (siteid,name,city) values
        (\$site_number, \'$site_name\',\'$site_city')");
}

#print $cgi->header();

#print <<EOF
#<html>
#<head>
#<title>Data has been uploaded
#<head>
#<body>
#Thank you.
#</body>
#</html>
#EOF
#;
}
exit;

[download]

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
by runrig (Abbot) on Sep 19, 2008 at 21:51 UTC

    Post the smallest program that displays the problem. If DBI is not your problem, don't post a ton of DBI related code...it is distracting.

    The Parse() method is supposed to return a hash reference...that's how many modules return objects in Perl...nothing wrong there.

    As mentioned previously, Use strict and warnings.

[reply]
Re: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
by graff (Chancellor) on Sep 20, 2008 at 04:11 UTC
    (shameless plug:) You might want to have a look at this utility that I posted at the Monastery a while back:

    xls2tsv

    It might give you some ideas for getting started with that whole "workbook" object thing.

[reply]
Re: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
by Narveson (Chaplain) on Sep 19, 2008 at 21:41 UTC
    I am attempting to convert a script I found on google to my purposes

    Good idea. The first step in the conversion is to put the following at the top of your program:

    use strict;
use warnings;

    [download]

    Then to get your program to compile you will need to declare all your variables. Declare them as lexicals using my.

    Then if you receive any warnings, read them and heed them.

    Then come back here and monks will be glad to help you.

[reply]
[d/l]
[select]
      I added the strict and warnings and heeded the ensuing warnings. I added my to my variables. I also eliminated any DBI and other distacting code from the script. The script compiles properly, but I still don't get any output from my print statements which implies the file is not getting parsed. 
      #!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $file = "\\lebensraum\\perl\\SAMReport.xls";

my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file)or die "
+Unable to open $file\n";

#locate columns in the spreadsheet from which we want to extract data
foreach my $sheet (@{$workbook->{worksheet}}) {
print "Sheet number $sheet\n";

    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
        if ($sheet->{Cells}[0][$col]->{Val} eq "Site Number") {
            my $siteid = $col;
            print "$siteid\n";}
        else {print "Could not find column Site Number\n";}

        if ($sheet->{Cells}[0][$col]->{Val} eq "Site Name") {
            my $name = $col;
            print "$name\n";}
        else {print "Could not find column Site Name\n";}

        if ($sheet->{Cells}[0][$col]->{Val} eq "City") {
            my $city = $col;
            print "$city\n";}
        else {print "Could not find column City\n";}
    }

#iterate through spreadsheet rows and extract site.siteid,site.name & 
+site.city

foreach my $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {

    my $site_number = $sheet->{Cells}[$row][my $siteid]->{Val};
    my $site_name = $sheet->{Cells}[$row][my $name]->{Val};
    my $site_city = $sheet->{Cells}[$row][my $city]->{Val};

    print "$site_number\n";
    print "$site_name\n";
    print "$site_city\n";


    }
}
exit;

      [download]
[reply]
[d/l]
[select]
        foreach my $sheet (@{$workbook->{worksheet}}) {
        Case sensitivity - should be @{$workbook->{Worksheet}}, not worksheet

        HTH

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom