Trying to load a colon separated file into a MySQL database table I ran into an error 1366.
1366 Error Incorrect Integer Value for Column 'MyIntegerColumn' at Row 1
The format of MyIntegerColumn was INT as you could imagine.
The point is that missing values in the .csv file where interpreted as an empty string (”) and MySQL 5.1 complains about putting a string record into an integer variable. I did not find any usefull solution via google: how to load missing values as NULL into an integer column.
The MySQL-Reference solved the problem:
Handling of NULL values varies according to the FIELDS and LINES options in use:
For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is “\”).
Since the ESCAPED BY character is “\” by default, it is sufficient to open the text/csv file and substitute two consecutive fieldseparators (‘,,’ e.g.) with ‘,\N,’. Save the file and repeat your loading syntax. In my case it was:
LOAD DATA LOCAL INFILE '/path/to/YourLoadFile.csv' INTO TABLE YourTargetTable FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Voila. Worked for me.