LOAD DATA INFILE Syntax Error

[DartmanX]

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

[Bill Karwin]

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.