Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Problem loading txt file into mysql

by Anonymous Monk
on Dec 29, 2005 at 09:05 UTC ( #519740=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I have a problem to uploading a text file into mysql.

The following errors is showing:

You have an error in your SQL syntax. Check the manual that correspon +ds to your MySQL server version for the right syntax to use near '"\r +"' at line 1 at C:/Apache2/cgi-bin/dbi.cgi line 13.

Here is my coding for your view

#!d:\perl\bin\perl use CGI qw(:standard); use CGI::Carp qw(warningsToBrowser fatalsToBrowser); use DBI; print header; print start_html("Results"); my $dbh=DBI->connect('DBI:mysql:test','root','') or die "Can't connect + $DBI::errstr"; my $sql=qw(load data local infile 'temp.txt' into table t1 FIELDS TERM +INATED BY ',' OPTIONALLY ENCLOSED BY """",LINES TERMINATED BY "\r"); #my $sql ="insert into t1 values('kushboo', 1)"; ## ITS WORKING my $sth=$dbh->prepare($sql) || die "$DBI::errstr"; $sth->execute || die "$DBI::errstr"; $dbh->disconnect; print end_html;

Thanks in Advance!!

Comment on Problem loading txt file into mysql
Select or Download Code
Re: Problem loading txt file into mysql
by spadacciniweb (Deacon) on Dec 29, 2005 at 09:39 UTC
    After
    my $sql=qw(load data local infile 'temp.txt' into table t1 FIELDS TERM +INATED BY ',' OPTIONALLY ENCLOSED BY """",LINES TERMINATED BY "\r");
    you can write
    print $sql; exit;
    bye!!
Re: Problem loading txt file into mysql
by Michalis (Pilgrim) on Dec 29, 2005 at 09:50 UTC
    Did you try to replace the double quotes ("") in \r with single quotes?
    --
    Michalis
Re: Problem loading txt file into mysql
by kulls (Hermit) on Dec 29, 2005 at 09:54 UTC
    try this,
    my $sql="load data local infile 'temp.txt' into table t1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"\"\"\",LINES TERMINATED BY '\r'";
    -kulls
Re: Problem loading txt file into mysql
by rnahi (Curate) on Dec 29, 2005 at 12:19 UTC
    qw stands for "quoted words", and it will produce a list.

    When you assign the result of qw to a scalar, it will get the last element of the list.

    $ perl -le '$x = qw(a b c); print $x' c $ perl -le '$x = q(a b c); print $x' a b c

    Replace qw() with q() and all will be well.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://519740]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (7)
As of 2014-07-25 23:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (175 votes), past polls