Re^2: Inserting CSV Into DB w/o CSV Modules

I want to use Text::CSV but I'm not very good at Perl yet and I'm getting confused on how to implement it. I've modified the following code but I'm not sure how to assign the fields in the CSV to my variables that are used in the SQL code. This is also my first time using files.

Here is what I came up with so far:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Text::CSV;

my $sth;

my $input="main_table.csv";

my $csv=Text::CSV->new();     

open(INFILE,$input) || die "Can't open file $input";

##Start database connections###############
my $database = "database";
my $db_server = "localhost";
my $user = "user";
my $password = "password";

my $dbh = DBI->connect("DBI:mysql:$database:$db_server", $user, $passw
+ord);

my $statement = "INSERT INTO table (state, city, location) VALUES (?,?
+,?)";

$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: $
+sth->errstr";

while(<INFILE>)
{
# I'm not sure where to go from here, need to put CSV fields into vari
+ables in SQL code?

$sth->execute($state,$city,$location) or die "Couldn't execute query: 
+$dbh->errstr";
}


$sth->finish;
$dbh->disconnect;

close(INFILE);
[download]

Thanks for all your help tachyon, your input is always very good.

Comment on Re^2: Inserting CSV Into DB w/o CSV Modules Download Code

Replies are listed 'Best First'.
Re^3: Inserting CSV Into DB w/o CSV Modules by tachyon (Chancellor) on Nov 24, 2004 at 00:21 UTC
`while(my $line = <INFILE> ) { $csv->parse($line); my ( $state,$city,$location ) = $csv->fields(); # assumming these + are col1,col2,col3 $sth->execute($state,$city,$location) or die "Couldn't execute que +ry: $dbh->errstr"; }` [download] This will be slow. It would be a lot faster to do: `my $sql =<<SQL; LOAD DATA LOCAL INFILE '$infile' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' SQL $dbh->do($sql);` [download] See LOAD DATA	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: Inserting CSV Into DB w/o CSV Modules
by tachyon (Chancellor) on Nov 24, 2004 at 00:21 UTC

while(my $line = <INFILE> ) {
    $csv->parse($line);
    my ( $state,$city,$location ) = $csv->fields();  # assumming these
+ are col1,col2,col3
    $sth->execute($state,$city,$location) or die "Couldn't execute que
+ry: $dbh->errstr";
}
[download]

This will be slow. It would be a lot faster to do:

my $sql =<<SQL;
LOAD DATA LOCAL INFILE '$infile' 
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
SQL

$dbh->do($sql);
[download]

See LOAD DATA

[reply]
[d/l]
[select]