Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet

by finhagen (Sexton)
on Sep 19, 2008 at 21:18 UTC ( #712634=perlquestion: print w/ replies, xml ) Need Help??
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)
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;

Comment on Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
Select or Download Code
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;

    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.

      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;
        foreach my $sheet (@{$workbook->{worksheet}}) {
        Case sensitivity - should be @{$workbook->{Worksheet}}, not worksheet

        HTH

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.

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://712634]
Approved by Narveson
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (14)
As of 2014-12-26 21:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls