[ILUG] MySQL and LOAD DATA

[Gavin McCullagh]

On Thu, 28 Feb 2002, Rory Winston wrote:

> Hi
> 
> I am using MySQL 3.23.47 and am trying to load a .CSV file with employee
> details into it. The exact command I am using is:
> 
> LOAD DATA INFILE "foo.csv" 
> INTO TABLE attendees (forename, surname, job_title, division) 
> FIELDS TERMINATED BY ','
> 
> However, I get ERROR 1064: You have an error in your SQL syntax near 'FIELDS
> TERMINATED BY ','.
> If I leave out the FIELDS TERMINATED BY clause it will load the data, but
> since it doesnt recognize the comma delimiter, I get all the data in one
> column!! Arrgh!!

Two things.  

1. No semi-colon at end.  This shouldn't cause a problem though

2. Syntax is wrong.  FIELDS TERMINATED BY comes before the definition  of fields you are
inserting into.  This is probably the problem.

Try this.

LOAD DATA INFILE "foo.csv" 
INTO TABLE attendees 
FIELDS TERMINATED BY ',' (forename, surname, job_title, division);


from the  mysql manual 

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    	[REPLACE | IGNORE]
	INTO TABLE tbl_name
	[FIELDS
		[TERMINATED BY '\t']
		[[OPTIONALLY] ENCLOSED BY '']
		[ESCAPED BY '\\' ]
	]
	[LINES TERMINATED BY '\n']
	[IGNORE number LINES]
	[(col_name,...)]

I'm pretty sure this is right anyway,

Gavin