user2642601 user2642601 - 6 months ago 20
Perl Question

Perl read from xls insert to mysql

I'm am pretty new to Perl and I'm writing a Perl script to read data from xls and insert the results to MySQL DB but i have problem...
here is my code:

#!/usr/local/bin/perl
use strict;
use warnings;
use diagnostics;
use Spreadsheet::ParseExcel;
use DBI;
use Data::Dumper qw(Dumper);
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('test.xls');
my $dbh = DBI->connect("dbi:mysql:parser", "root", "123qwe", { RaiseError => 1}) or die $DBI::errstr;
my $query = 'INSERT INTO parser (Name,Country) VALUES (?,?)';
my $sth = $dbh->prepare($query) or die "Prepare failed: " . $dbh->errstr();

if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
for my $worksheet ( $workbook->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
my $results = $cell->value();
open(my $fh, '>>', "test");
print $fh "$results\t";
close $fh;
}
}
}

open my $fh, "<", "test" or die $!;
while (<$fh>)
{
chomp;
my @vals = split;
$sth->execute(@vals);
}
close $fh;


So when i execute the script it ends with the following error:

DBD::mysql::st execute failed: called with 6 bind variables when 2 are needed at ./parser.pl line 39, <$fh> line 1.
Uncaught exception from user code:
DBD::mysql::st execute failed: called with 6 bind variables when 2 are needed at ./parser.pl line 39, <$fh> line 1.


Which is natural because i have indeed 6 variables in the output:

John Smith USA Ognyan Penkov Egypt


So the problem is that i cant seem to find a way to split the results from every column/row and put them in the MySQL tables because the Spreadsheet::ParseExcel reads all the date as 1 row.(For example the names must go to table Name and the country to table country)

My XLS file looks like this:

A B
1. John Smith USA
2. Ognyan Penkov Egypt
...etc...


My MySQL tables:

+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(6) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | YES | | NULL | |
|Country | varchar(255) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+

Answer

You are opening a file called test for append writing for every single cell in the document. You then add the current cell's value, followed by a tabulator \t character. Afterwards you open and read that same file line by line (but there is only one line), chomp off a line ending that is not there (because you didn't put one) and split on whitespace, because split without a delimiter uses \s, which is a single whitespace.

If omitted, PATTERN defaults to a single space, " " , triggering the previously described awk emulation.

And that's exactly the problem, because your file looks like this:

John\tSmith\tUSA\tOgnyan\tPenkov\tEgypt

The \t are single whitespaces, so you end up with @val being all of those. And if you pass that to your query, it fails.

Since you do not, as you say, have a table for names and a table for countries, but instead all you do is put the data from the Excel file into a single MySQL table row by row, you can just do that in your $row loop.

for my $worksheet ( $workbook->worksheets() ) {
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {
        my @values;
        for my $col ( $col_min .. $col_max ) {
            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;
            push @values, $cell->value();
        }
        $sth->execute(@values) or die $dbh->errstr;
    }
}
Comments