in reply to Inserting CSV Into DB w/o CSV Modules

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

[download]

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.

cheers

tachyon

Replies are listed 'Best First'.
Re^2: Inserting CSV Into DB w/o CSV Modules
by matija (Priest) on Nov 23, 2004 at 07:26 UTC
    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.
[reply]
Re^2: Inserting CSV Into DB w/o CSV Modules
by awohld (Hermit) on Nov 23, 2004 at 16:32 UTC
    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.
[reply]
[d/l]
      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]

In Section Seekers of Perl Wisdom