Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re^6: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet

by finhagen (Sexton)
on Sep 22, 2008 at 00:43 UTC ( #712912=note: print w/ replies, xml ) Need Help??


in reply to Re^5: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
in thread Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet

Thanks so much. Using our $var did the trick and my script is functioning properly now. Here is the final version:

#!/usr/bin/perl use strict; use warnings; use DBI; use DBD::mysql; use Spreadsheet::ParseExcel; # DBI configuration variables my $platform = "mysql"; my $database = "smdb"; my $host = "Sinn"; my $port = "3306"; my $tablename = "site"; my $user = "user"; my $pw = "password"; #Data source name my $dsn = "dbi:mysql:smdb:localhost:3306"; # PERL DBI connection handle my $dbh = DBI->connect($dsn, $user, $pw)or die "Unable to connect: $DB +I::errstr\n"; #xls file to be parsed my $file = "SAMReport.xls"; #global variables our $siteid; our $name; our $address; our $city; our $state; our $zip; 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") { $siteid = $col; print "$siteid\n"; } if ($sheet->{Cells}[0][$col]->{Val} eq "Site Name") { $name = $col; print "$name\n"; } if ($sheet->{Cells}[0][$col]->{Val} eq "Address") { $address = $col; print "$address\n"; } if ($sheet->{Cells}[0][$col]->{Val} eq "City") { $city = $col; print "$city\n"; } if ($sheet->{Cells}[0][$col]->{Val} eq "State") { $state = $col; print "$state\n"; } if ($sheet->{Cells}[0][$col]->{Val} eq "Zip Code") { $zip = $col; print "$zip\n"; } } #iterate through spreadsheet rows and extract site.siteid, site.name, +site.address, site.city, site.state, site.zip foreach my $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) { my $site_number = $sheet->{Cells}[$row][$siteid]->{Val}; my $site_name = $sheet->{Cells}[$row][$name]->{Val}; my $site_address = $sheet->{Cells}[$row][$address]->{Val}; my $site_city = $sheet->{Cells}[$row][$city]->{Val}; my $site_state = $sheet->{Cells}[$row][$state]->{Val}; my $site_zip = $sheet->{Cells}[$row][$zip]->{Val}; $dbh->do("insert into site (indate,sitei +d,name,address,city,state,zip) values (CURDATE(),\'$site_number\',\'$site_name\',\'$site_address +\',\'$site_city',\'$site_state\',\'$site_zip\')"); } } exit;


Comment on Re^6: Spreadsheet::ParseExcel Script Fails to Parse (access) Excel Spreadsheet
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (11)
As of 2015-07-29 12:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (263 votes), past polls