Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
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 lurking in the Monastery: (10)
As of 2014-10-21 06:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (96 votes), past polls