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

Dear Wise!

I have a perl script designed to insert/update database table from csv file. It is also meant to be quite universal, so it has not be edited every time it would be used to upload data into different databases

The code is invoked by entering parameters from command line, something like:

 ./scriptname.pl -ignore-lines=1 --table=affiliates --user=username --password=password --dbname=test --delete --fields-terminated-by="," file.csv

The problem is that I'm getting following error

Use of uninitialized value in join or string at ./scriptname.pl line 8
+1, <$fh> line 2.
DBD::mysql::st execute failed: Column count doesn't match value count 
+at row 1 at ./scriptname.pl line 83, <$fh> line 2.
Failed to write row Column count doesn't match value count at row 1

[download]

and cannot really figure out what should be done to get this script to work as it should. I would really appreciate if you would look at the code below and shed some light on me.

#!/usr/bin/perl
#
# initial comments section
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
# end of initial comments section
#
use strict;
use warnings;
use DBI;
use Getopt::Long;
use Text::CSV;


my $delete = "";
my $table = "";
my $dbname = "";
my $user = "";
my $password = "";
my $terminator = ",";
my $ignore = 0;
my $optenclosed = '"';
my $db;
my $prep;
my $fh;

GetOptions('delete' => \$delete,
            'table=s' =>\$table,
            'dbname=s' => \$dbname,
            'user=s' => \$user,
            'password=s' => \$password,
            'fields-terminated-by=s' => \$terminator,
            'ignore-lines=i' => \$ignore,
            'fields-optionally-enclosed-by=s' => \$optenclosed);


# check there is the mandatory options
if ($table eq '' or $dbname eq '' or $user eq '' or $password eq '' or
+ $#ARGV == -1) {
    die("Incorrect options: Need tablename, database, user, password a
+nd at least 1 filename\n");
}

# open the database connection
$db = DBI->connect("DBI:mysql:$dbname", $user, $password) or die "Cann
+ot open database\n";

# delete the contents of the table if delete is set
if ($delete) {
     $prep = $db->prepare("DELETE FROM $table") or die "Cannot prepare
+ database " . $db->errstr() . "\n";
     if (!$prep->execute()) { die "Failed to delete rows" . $db->errst
+r() . "\n"; }
}

# create the csv object
my $csv = Text::CSV->new({binary => 1,
                        quote_char => $optenclosed,
                        sep_char => $terminator});

# main loop for each of the passed files
foreach my $argnum (0..$#ARGV) {
    if (!open($fh, '<', $ARGV[$argnum])) {
        print "Warning: Could not open " . $ARGV[$argnum] . " so file 
+skipped\n";
    } else {
        # if there is an ignore setting, loop around discarding those 
+lines
        for(my $i = 0; $i < $ignore; $i++) {
            my $row = $csv->getline($fh);
        }

        # now perform the database write
        while (my $row = $csv->getline($fh)) {
            $prep = $db->prepare("INSERT INTO $table VALUES (\"" . joi
+n('","', $csv->fields()) . "\")")
                    or die "Cannot prepare database " . $db->errstr() 
+. "\n";
            if (!$prep->execute()) { die "Failed to write row " . $db-
+>errstr() . "\n"; }
        }
        $csv->eof or $csv->error_diag();
        print "Successfully written $dbname into $table using " . $ARG
+V[$argnum]. "\n";
    }
}

[download]

any suggestions would help since I am just starting my adventures with perl

Replies are listed 'Best First'.
Re: Use of uninitialized value in join or string
by roboticus (Chancellor) on Jan 29, 2014 at 11:45 UTC

    domaniqs:

    I'm guessing that some of your data has embedded quotes and/or commas in it. That has an opportunity to confuse the CSV handler *and* your database handling code. In this case, it appears to be your database handling code.

    The difficulty is getting your data properly quoted for use with the database. Luckily, it's such a common difficulty that DBI has support for it built in: Placeholders. Rather than building the value strings into your SQL, you use a question mark as a placeholder. Then, when you execute the statement, you provide an array containing the values for the question marks, in order. So I'd suggest modifying your program to something like:

    my $SQL = "INSERT INTO $table VALUES (" 
        . join(",", map { "?" } $csv->fields)
        . ")";
$prep = $db->prepare($SQL) or die "...";
while (my $row = $csv->getline($fh)) {
    $prep->execute($csv->fields);
}

    [download]

    The first bit build a string with a set of ? placeholders for the fields. It does so by using the map operator to convert the list of fields into a list of question marks, and join to add the commas between then.

    Then we prepare the statement for execution. Notice one thing: The prepare statement is *outside* your loop. We can do this because the statement doesn't change for each iteration!

    So if your database drivers support precompiling statements (and most do), then you get a speed boost because it only compiles the statement one time! Finally, in the loop we simply execute the statement with a different list of values each time.

    Please learn to use placeholders, and you'll find a lot of database work simpler, safer and more reliable.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]

      Many thanks for explaining how useful placeholders are to me, @roboticus

      I have replaced code:

              while (my $row = $csv->getline($fh)) {
            $prep = $db->prepare("INSERT INTO $table VALUES (\"" . joi
+n('","', $csv->fields()) . "\")")
                    or die "Cannot prepare database " . $db->errstr() 
+. "\n";
            if (!$prep->execute()) { die "Failed to write row " . $db-
+>errstr() . "\n"; }
        }

      [download]

      with the code that you have suggested to me, but the script produces another error:

      DBD::mysql::st execute failed: Column count doesn't match value count at row 1 at ./test.pl line 85, <$fh> line 2.

      and so on for every record of the file to be imported.

      which makes no sense to me, as from my understanding when I use map { expr } list, it evaluates expr for every item in list, so it should update the list automatically, isn't that right?

[reply]
[d/l]
[select]

        Your code snippet here isn't actually using placeholders, so you may not have quite grasped what they are. See how roboticus is using the question marks in his SQL? See how he calls execute with the field list? These are things which you should be doing.

[reply]
[d/l]
Re: Use of uninitialized value in join or string
by BrowserUk (Patriarch) on Jan 29, 2014 at 11:46 UTC
    Use of uninitialized value in join or string at ./scriptname.pl line 81,

    That's strange! The error is listed as line 81, but the script you've posted only has 71 lines?

    With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.
[reply]
[d/l]

      I am sorry, I deleted initial comments section that is left for brief description what the script actually do.

      Please have a look at updated post without deleted comments section

[reply]

Back to Seekers of Perl Wisdom