CODEWITHSUNDEEP
mysqlcsvimport
Jakob Jingleheimer
Jakob Jingleheimer

Reputation: 31580

mysql> error 1265: data truncated for column 'xxx' at row 1

I'm trying to import a csv file into a table in a mysql database. The table has 141 columns with datatypes of INT, VARCHAR(20), TIMESTAMP,TIMESTAMP, and then a series of TINYTEXTs and VARCHAR(4)s.

I'm getting 7 data truncated errors on columns with datatype VARCHAR(4) for which the data does not exceed 4 characters.

I tried forcing it to continue with IGNORE, but then it went crazy and chopped data from other cells into pieces and scattered them throughout the table.

I'm using the command-line on an MS 2008 R2 Server to run the SQL and the csv file is located in the Db's directory.

P.S. I did read several of the other posts and google results related to mysql+import or mysql+data truncated (1265), but they didn't seem to cover this particular issue.

Thanks!

EDIT: I'm pretty sure it has something to do with the LOAD DATA function because if I INSERT one row at a time, it works just fine…

My sql for the import is:

LOAD DATA INFILE '2011-09.csv' IGNORE INTO TABLE `survey`.`2011-09` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Upvotes: 1

Views: 14409

Answers (1)

Jakob Jingleheimer
Jakob Jingleheimer

Reputation: 31580

During an INSERT, MySQL will tell you if you have too many columns. But apparently MySQL is not very kind: during a LOAD it does not tell you if you have too many columns.

Upvotes: 1

Related Questions