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

Problem Solved... Hello all, I'm having trouble getting a CSV file to correctly import into MySQL using the following code. The first line gets put in and then stops completely. I've included some of the CSV file. Any help would be greatly appreciated. This is my first message on Perl Monks! Here is the code I'm using: 
#!/usr/bin/perl

print "Content-type: text/html\n\n";
use warnings;                                
use CGI::Carp qw(fatalsToBrowser);    
use strict;
use CGI;
use Data::Dumper;                            

#------------------------- declare --------------------------

    my ($sth, $stmt); 
    my $query = new CGI;
    my $file    = $query->param('file');                
    my $table= $query->param('table');

#------------------------- connect --------------------------
    
    my $databasename     = "go_perl"; 
    my $username         = "uname";
    my $password         = "pword"; 
    
    use DBI qw(:sql_types); 
    my $dbh = DBI->connect("DBI:mysql:".$databasename, $username, $pas
+sword, 
    { RaiseError => 1},) or die "Connect failed: $DBI::errstr\n";

    
#--------------------- open .db file and insert 

    $stmt =qq/LOAD DATA LOCAL INFILE "..\/file.csv" 
                INTO TABLE table_name 
                FIELDS TERMINATED BY ","
                OPTIONALLY ENCLOSED BY """"
                LINES TERMINATED BY "\r" 
                IGNORE 1 LINES /;
    execute_it($dbh,$stmt);
    close_db($dbh, $sth);    
   exit();

[download]
And then the execute and close subs. Sample of the CSV file, note, I artificially seperate each of these lines so the could be seen better: 
1,"01",0,"0000","00","0000000","Name Christine L","0000","Full","10",3
+5000,"10","100","0150","3","17","047","Teacher"

2,"01",0,"0000","00","0000000","Name Suzanne H","0000","Full","75",400
+70,"12","100","0070","3","25","047","Admin"

3,"01",0,"0000","00","0000000","Name Lorraine","0000","Full","09",4498
+0,"10","100","0200","4","17","093","Teacher"

4,"01",0,"0000","00","0000000","Name Laurie","0000","Full","75",45760,
+"12","100","0090","5","17","047","Admin"

[download]
"Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness

Replies are listed 'Best First'.
Re: Problem uploading 'large' CSV file using DBI
by diotalevi (Canon) on Jul 26, 2005 at 23:27 UTC
    We're missing the part where you show us what error occurred, what data was loaded, and the contents of execute_it() and close_db().
[reply]
      I'm not receiving any type of error message. When I check the SQL table after running the script only the first line appears with a snippet of the second line's information appearing in the last column.

      Teacher"2,"01
      appears in the last column. The snippet of the second line is: 2,"01 (Teacher" from the first line) in the last column and then no other information is input into the database. Is there any more information that is needed? 
      
#------------------------------  db operations 

sub execute_it {
    my ($dbh, $stmt) = @_;

     print "Content-type: text/html\n\n";
     print "Here1".Dumper ($dbh, $stmt);

    $sth = $dbh->prepare($stmt);
    $sth->execute();
    return ($sth);
}

Content-type: text/html Here1

$VAR1 = bless( {}, 'DBI::db' ); 

$VAR2 = 'LOAD DATA LOCAL INFILE "../file.csv" INTO TABLE table_name FI
+ELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """" LINES TERMINATED B
+Y "\r" IGNORE 1 LINES ';

sub close_db {
    my ($dbh, $sth) = shift;
    if ($sth) {
        $sth->finish();
        $dbh->disconnect();    
    }
}

      [download]
      "Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness
[reply]
[d/l]
        Perhaps your line endings aren't Mac-ish like \r. Perhaps they're UNIXy like \n or DOSish like \r\n.
[reply]
Re: Problem uploading 'large' CSV file using DBI
by BUU (Prior) on Jul 27, 2005 at 00:56 UTC
    What happens when you run the sql 'manually' from the mysql shell or what not?
[reply]
      No errors, nothing, just loads that first line like above. 
      %mysql > load data local infile '/home/champ1/public_html/admin/dump/t
+eachers1999.csv' into table salary_teacher_1999 fields terminated by 
+"," optionally enclosed by """" lines termintated by "\r" ;
%

      [download]
      I'm beginning to think it might be best for me to wrap all the fields in "" instead of just the strings and add ; as an ending just to insure nothing is being missed.
      "Recognizing who we aren't is only the first step toward knowing who we are." - Os Guinness
[reply]
[d/l]
Re: Problem uploading 'large' CSV file using DBI
by rnahi (Curate) on Jul 27, 2005 at 06:43 UTC

    I believe that we are missing some valuable piece of information here.

    Please read Before asking a database related question .... There is plenty of useful advice that you can use for either solving the problem on your own or presenting a better case for the Monastery.

[reply]
      I'll read through that and see if I find anything different or valuable information currently missing. This is my first node, I'm still learning the ropes, thanks for the link.
[reply]
Re: Problem uploading 'large' CSV file using DBI
by sk (Curate) on Jul 27, 2005 at 04:28 UTC
    Sorry I don't have MYSQL up and running now so cannot test out the command/data myself.

    As BUU suggested you should first try to debug the result by directly importing the data FROM MySQL. Once you do that MySQL's  show waarnings should tell you the problem.

    Since you are executing the load data command directly, I would be surprised if this problem is Perl related.

    -SK

[reply]
[d/l]
Re: Problem uploading 'large' CSV file using DBI
by Brovnik (Hermit) on Jul 27, 2005 at 12:55 UTC
    Most likely there is a MYSQL error.
    1. Is the table really called table_name ?
    2. Looks like you are trying to grab the table name from the CGI input into $table, but then you don't do anything with it.

    --
    Brovnik
[reply]
      I was grabbing the table name from CGI until the problem started.

      Then I just put it in the mysql command line directly. The table is salary_teacher_1999. I was just using table_name as an example since I've got about 50 tables to go through.
[reply]

Back to Seekers of Perl Wisdom