Need help with LOAD statement.

[cb49747]

I have a csv file that looks like this

field2,field3,field4,field5

I using the following sql statement to place this into a table.

LOAD DATA LOCAL INFILE \'$df\' INTO TABLE $table FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\'  ESCAPED BY \'\\\\' LINES TERMINATED BY \'\\r\\n\'

The problem I'm having is that field1 is a autoincremented field thus the load does not work. However if I add a comma before the field2 in the csv file such as below it works fine.

,field2,field3,field4,field5

The problem with this is that the file is not generated this way and since there is lots of records and not computer lit person performing the task, adding the leading comma is not a feasable solution.

I have tried changing the load statement to the below, but still did not work.

LOAD DATA LOCAL INFILE \'$df\' INTO TABLE $table ('field2','field3','field4', 'field5') FIELDS TERMINATED BY \',\' ENCLOSED BY \'\"\'  ESCAPED BY \'\\\\' LINES TERMINATED BY \'\\r\\n\'

Any help would be greatly appreciated.

 

[sleipnir214]

First, I recommend that you review the documentation of the LOAD DATA query here:

http://dev.mysql.com/doc/refman/4.1/en/load-data.html

Second, drop the "INFILE" clause. It's only used when the data is transmitted through the database connection. By default, communication libraries and MySQL have this functionality turned off.

Third, you can load data into an auto_increment column. But if you want to specify the columns into which the data should be loaded. You do this by specifying the column names at the end of the file:

LOAD DATA filename
INTO TABLE tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(columnname2, columname3, columnname4)

The above query would read the filename and load the first piece of data from each line into columnname2, the second into columnnname2, etc.

Fourth, you are using the "FIELDS ENCLOSED BY" clause, but your example data does not show quotes around the data. I do not know for sure, but this may be problematic.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

I'm sorry, I was wrong.

Keep "INFILE", it's "LOCAL" that causes the data to be transmitted through the connection.

Want the best answers? Ask the best questions!

TANSTAAFL!!