MySQL Error 1366: Incorrect Integer Value for Column


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.

One thought on “MySQL Error 1366: Incorrect Integer Value for Column

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s