> My results are in a comma delimited file. I need to read
> through the file
> adding each line as a record in the table using the SQL:
>
> my $query1=<<"QUERY";
> INSERT INTO DIFFAMNTS VALUES
> ('PK',9197,171509,'THIS IS A TEST2
> ROW','AA',1,77120,101032)
> QUERY
>
> Obviously thats a test line and the values I have in the
> insert would need
> to be replaced with variables from the csv file. I'll
> probably create those
> with a simple split.
If it's the simple case (CSV file was computer-generated and contains no
extraneous quotes or commas so you can depend on all the data being in the
right place and format), you will find placeholders are your friend:
#... standard DBI connect stuff to get $dbh database handle
$sth = $dbh->prepare(<<EOM);
INSERT INTO DIFFAMNTS
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
EOM
open FILE, "<myfile.csv";
while(<FILE>)
{
chomp;
$sth->execute(split /,/);
#cool eh? :)
}
# etc...
Commentary:
The "while(<FILE>)" bit reads your csv file in one line at a time, and
assigns the line to the $_ variable.
The "chomp" strips off the trailing newline.
The "split /,/" takes the $_ variable and splits it into an array of data
values, dividing up at the commas.
Passing a list of arguments to "$sth->execute()" causes DBI to substitute
one data value for each "?" placeholder in the original prepared query, so
your data is pumped in more or less intact.
Caveats:
What about data values with quotes around them? Placeholders don't use
quotes, so if your data value is enclosed in quotes, the quotes will be
inserted in the database as part of the data. Oops! Probably not what you
want. Let's try this:
while(<FILE>)
{
chomp;
my @data = split /,/;
my @good_data = grep s/^'?(.*?)'?$/$1/, @data;
$sth->execute(@good_data);
}
The grep runs through each item in @data and strips off any leading and
trailing single quotes. Notice the bit in the middle that goes (.*?). By
default, the (.*) operator is "greedy" and tries to match the biggest string
it can, so /^'?(.*)'?$/ will essentially ignore the optional '? match at the
end and will include the trailing quote as part of the match for (.*). By
using (.*?), the "non-greedy" match, we match only what's inside the quotes
(if there are quotes).
Of course, a real HCPH (Hard Core Perl Hacker) would probably do something
more like
while(<FILE>)
{
chomp;
$sth->execute(
grep s/^'?(.*?)'?$/$1/,
split /,/);
}
...which is the same thing without the temporary variables. :)
Mark Nutter
Manager, Internet Applications Development
Marconi
[EMAIL PROTECTED]
It's not necessarily an advantage to have better brakes than the guy behind
you.
