MySQL ODBC Connector - LOAD DATA INFILE Syntax Error

Author

LOAD DATA INFILE Syntax Error

Jason Ferguson

2005-09-26, 8:24 pm

------ =_Part_7330_28183971
.1127783036645
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

1923158|GA|1996 Olympic Yachting
Cauldron|park|Chatha
m|13|051 |320446N|0810502W|32
.07944|-
81. 08389||||||||Savanna
h

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,LOCTY
PE,@d,STATE,COUNTY,@
d,@d,LNG,LAT,@d,@d,@
d,@d,@d,@d,@d,@=
=3D
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,L
NG,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

------ =_Part_7330_28183971
.1127783036645--

Robert L Cochran

2005-09-27, 3:23 am

http://mirror.tomato.it/mysql/doc/m.../load-data.html

Bob Cochran

Jason Ferguson wrote:

>I am attempting to import a large file with data in this format:
>
>1923158|GA|1996 Olympic Yachting
> Cauldron|park|Chatha
m|13|051 |320446N|0810502W|32
.07944|-
>81. 08389||||||||Savanna
h
>
>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,LOCTY
PE,@d,STATE,COUNTY,@
d,@d,LNG,LAT,@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,L
NG,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
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jasper Bryant-Greene

2005-09-27, 3:23 am

Robert L Cochran wrote:
> http://mirror.tomato.it/mysql/doc/m.../load-data.html

There's a lot to read there for one small paragraph, so from the above link:

"Before MySQL 5.0.3, the column list must contain only names of columns
in the table being loaded, and the SET clause is not supported. As of
MySQL 5.0.3, the column list can contain either column names or user
variables, and the SET clause is supported. This enables you to assign
input values to user variables, and then perform transformations on
those values before assigning the result to columns."

Bob: please don't top-post.

Jasper

> Jason Ferguson wrote:
>
>
>

--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jason Ferguson

2005-09-27, 3:23 am

------ =_Part_7354_20226777
.1127785051311
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I've been over that page before posting, with no luck. It might be an
obvious error in syntax, but I can't figure it out.

Jason

On 9/26/05, Robert L Cochran <cochranb@speakeasy.net> wrote:
>
> http://mirror.tomato.it/mysql/doc/m.../load-data.html
>
> Bob Cochran
>
>
> Jason Ferguson wrote:
>
>
d,@=3D[color=darkred
]
> manual
r[color=darkred]
> at
> a
>
>

------ =_Part_7354_20226777
.1127785051311--

Jason Ferguson

2005-09-27, 3:23 am

------ =_Part_7370_28622101
.1127785275219
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Okay, now I get it. I was using the 4.1 series. Looks like an upgrade is in
order.

Jason

On 9/26/05, Jasper Bryant-Greene <jasper@bryant-greene.name> wrote:
>
> Robert L Cochran wrote:
>
> There's a lot to read there for one small paragraph, so from the above
> link:
>
> "Before MySQL 5.0.3, the column list must contain only names of columns
> in the table being loaded, and the SET clause is not supported. As of
> MySQL 5.0.3, the column list can contain either column names or user
> variables, and the SET clause is supported. This enables you to assign
> input values to user variables, and then perform transformations on
> those values before assigning the result to columns."
>
> Bob: please don't top-post.
>
> Jasper
>
> (@d,@d,LOCNAME,LOCTY
PE,@d,STATE,COUNTY,@
d,@d,LNG,LAT,@d,@d,@
d,@d,@d,@d,@d=
,@=3D

e[color=darkred]
> near
> ,LOCNAME,LOCTYPE,@d,
STATE,COUNTY,@d,@d,L
NG,LAT,@d,@d,@d,@d,@
d,@d,@d,@d)'
>
> --
> Jasper Bryant-Greene
> Freelance web developer
> http://jasper.bryant-greene.name/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...br /> n@gmail.=
com
>
>

------ =_Part_7370_28622101
.1127785275219--

Sponsored Links