Re: Inserting CSV Into DB w/o CSV Modules

[tachyon]

Can someone show me a generic or other way to load CSV files in my code that doesn't need Tie::Handle::CSV; or any other CSV module?

Not using a module is a bad idea. If you have shell access why not read A Guide to Installing Modules and do a local install? If you don't have shell access why not just pick a CSV module like Text::CSV which is pure perl, then just do this:

1. Make a directory in your cgi-bin called say LIB.
2. Make a directory in your cig-bin/LIB dir called Text
3. Copy CSV.pm from the Text::CSV distribution to cgi-bin/LIB/Text/CSV.pm

Now all you have to do is

use lib './LIB';  # assumes current working dir is cgi-bin
use Text::CSV;
# yada

If you don't like either of those worthy ideas then just rip the guts out of Text::CSV and stuff it in your code.

[matija]

And note that if you _don't_ have shell access, but your scripts have sufficient permissions to write to disk, you can create a short CGI script that will install whatever module into your local disc space. The CPAN module offers its functions not just as commands available from the shell, but also as functions you can call from your program if need be.

[awohld]

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);

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

[tachyon]

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";
}

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);

See LOAD DATA