Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
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 scrutinizing the Monastery: (13)
As of 2014-09-16 17:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (37 votes), past polls