Problem uploading 'large' CSV file using DBI

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

Comment on Problem uploading 'large' CSV file using DBI Select or Download Code

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]
Re^2: Problem uploading 'large' CSV file using DBI by Tatnall (Beadle) on Jul 26, 2005 at 23:55 UTC
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]
Re^3: Problem uploading 'large' CSV file using DBI by diotalevi (Canon) on Jul 27, 2005 at 03:49 UTC
Perhaps your line endings aren't Mac-ish like \r. Perhaps they're UNIXy like \n or DOSish like \r\n.	[reply]
Re^4: Problem uploading 'large' CSV file using DBI by Tatnall (Beadle) on Jul 27, 2005 at 14:57 UTC
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]
Re^2: Problem uploading 'large' CSV file using DBI by Tatnall (Beadle) on Jul 27, 2005 at 14:47 UTC
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]
Re^2: Problem uploading 'large' CSV file using DBI by Tatnall (Beadle) on Jul 27, 2005 at 14:53 UTC
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]
Re^2: Problem uploading 'large' CSV file using DBI by Tatnall (Beadle) on Jul 27, 2005 at 14:50 UTC
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]


Welcome to the Monastery
	PerlMonks