1

I am trying to load data from a CSV into a database in MySQL workbench. The table I am loading into has an auto increment ID column. I am trying to get the query to recognize that I want it to keep the first column as Null, and I put NULL as the value in the CSV, but I cannot get the SET ... NULL command to recognize the name of the ID column. Here is the SQL I am using:

load data infile 'filenam.csv'
INTO TABLE table_name  
fields Terminated By ',' 
LINES TERMINATED BY ',,'
SET column_name = null

I suspect I am making a simple syntax error that is causing the problem. But I cannot figure out what it is.

stevenspiel
  • 5,775
  • 13
  • 60
  • 89
user3119955
  • 11
  • 2

1 Answers1

0

If you put NULL as the value in the CSV file then you shouldn't need the "SET column_name = null" in the statement. AFAIK, the SET value should be used to supply values not derived from the input file or to perform calculations to the value before insertion. This statement should work fine since you said you specified NULL in the CSV. However, make sure you specified NULL "properly" according to the documentation. I always use \N in my import files.

LOAD DATA INFILE 'filename.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',,'

Secondly, you can discard the NULL specified in the CSV file by assigning it to a user variable and then specifying the column value with SET. However, you need to specify a column list 

LOAD DATA INFILE 'filename.csv'
INTO TABLE table_name (@dummy, column_2, column_3)
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',,'
SET column_name = NULL

I have one other thought based on the MySQL docs and dependent upon how your server is configured. Comment if this does not work and I will provide more options.

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

Hideous1
  • 126
  • 7