469,921 Members | 2,152 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,921 developers. It's quick & easy.

LOAD DATA INFILE Syntax Error

I am attempting to import a large file with data in this format:

1923158|GA|1996 Olympic Yachting
Cauldron|park|Chatham|13|051 |320446N|0810502W|32.07944|-
81.08389||||||||Savannah

With this command:
LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt'
INTO TABLE locations2
FIELDS TERMINATED BY '|'
LINES TERMINATED BY "\r"
(@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,L AT,@d,@d,@d,@d,@d,@d,@d,@=
d);

The @d is a dummy variable for information I do not need to import. The
table structure looks like this:

+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| STATE | int(11) | YES | | NULL | |
| LOCNAME | varchar(25) | YES | | NULL | |
| LOCTYPE | varchar(10) | YES | | NULL | |
| COUNTY | int(11) | YES | | NULL | |
| CTRLAT | float | YES | | NULL | |
| CTRLNG | float | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+

And here is the error I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that
corresponds to your MySQL server version for the right syntax to use
near
'@d,@d
,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,LAT,@d, @d,@d,@d,@d,@d,@d,@d)'
at
line
5

This error is driving me nuts! Any help would be appreciated, as this
is a
rather large file.

Jason

Sep 27 '05 #1
1 5761
DartmanX wrote:
LOAD DATA LOCAL INFILE 'C:\\PHP\\FL_DECI.txt'
INTO TABLE locations2
FIELDS TERMINATED BY '|'
LINES TERMINATED BY "\r"
(@d,@d,LOCNAME,LOCTYPE,@d,STATE,COUNTY,@d,@d,LNG,L AT,@d,@d,@d,@d,@d,@d,@d,@=
d);

The @d is a dummy variable for information I do not need to import.


Are you using MySQL 5.0.3 or later? Putting imported data into user
variables is not supported in releases earlier than 5.0.3, according to
this page:
http://dev.mysql.com/doc/mysql/en/load-data.html

"As of MySQL 5.0.3, the column list can contain either column names or
user variables..."

If you're using an earlier release of MySQL, I'd suggest creating a
temporary table with enough columns to store all the fields in your txt
file, and then once it's imported, you can copy the columns you need to
your locations2 table with INSERT...SELECT.

Another option is that you could install a copy of the latest MySQL 5.0
release candidate on another computer (or the same computer, if you're
feeling intrepid about maintaining two instances of MySQL on one host),
then import your text file using the @d dummy variables technique, then
make a SQL dump file of the resulting table, so it's easier to import to
your 'real' MySQL instance.

Regards,
Bill K.
Sep 27 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bruce A. Julseth | last post: by
3 posts views Thread by Otto | last post: by
reply views Thread by Donald Tyler | last post: by
1 post views Thread by Jami Bradley | last post: by
reply views Thread by Rajesh Kapur | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.