Mysql 'LOAD DATA INFILE' command error

talat · 01-07-2008, 08:41 AM

Hi Guys,

Consider that i have extracted the data from table 'users' with the following command.

select localpart,username,clear INTO OUTFILE '/tmp/new_userdata1.txt' FIELDS TERMINATED BY ',' FROM users where username LIKE '%domain.net.pk';

Now when i try to load this data in the table 'users' with following command.

LOAD DATA INFILE '/tmp/new_userdata.txt' INTO TABLE users(localpart,username,clear) SET domain_id=19 FIELDS TERMINATED BY ',';

It gave me following error.

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 'FIELDS TERMINATED BY ','' at line 1

Can you plz tell me how to resolve it . I have run this command without 'FIELDS TERMINATED BY' clause and command successfully runs but did not put the desire record in the table. Plz help.

Regards

PTrenholme · 01-07-2008, 10:46 AM

Try SET domain_id='19'.

From the manual:

Quote:

`LOAD DATA INFILE' regards all data as strings, so you cannot use
numeric values for `ENUM' or `SET' columns the way you can with
`INSERT' statements.

talat · 01-07-2008, 10:58 AM

Hi

There was one mistake in my cmd i.e the SET clause comes at the end. But still after changing my cmd i am getting error.

LOAD DATA INFILE '/tmp/new_userdata1.txt' INTO TABLE users(localpart,username,clear) FIELDS TERMINATED BY ',' SET domain_id='19' ;

Following is the error.

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 'FIELDS TERMINATED BY ',' SET domain_id='19'' at line 1

Regards

PTrenholme · 01-07-2008, 09:09 PM

Try

Quote:

LOAD DATA INFILE '/tmp/new_userdata.txt' INTO TABLE users SET domain_id='19' FIELDS TERMINATED BY ',' (localpart,username,clear);

I believe SET and FIELDS qualify TABLE not the column list.

<edit>
You know, you could have avoided this whole problem by using this select:

Quote:

select '19',localpart,username,clear INTO OUTFILE '/tmp/new_userdata1.txt' FIELDS TERMINATED BY ',' FROM users where username LIKE '%domain.net.pk';

to create your file, and

Quote:

LOAD DATA INFILE '/tmp/new_userdata.txt' INTO TABLE users FIELDS TERMINATED BY ',' (domain_id,localpart,username,clear);

to read it back in.
</edit>