CODEWITHSUNDEEP
mysqlcsv
Xavier Peña
Xavier Peña

Reputation: 7919

Load data infile (from csv): data too long for column 'xxx' at row N

I am importing a .csv into Mysql like so:

LOAD DATA INFILE '(...)/contact_acquisition.csv'
INTO TABLE rs_ui_db.contact_acquisition
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

...which throws the following error:

"Data too long for column 'comments' at row 63"

I look at row 63 of the .csv, and "comments" has a length of 115.

The create statement seems OK (with comments char(255)) :

CREATE DATABASE IF NOT EXISTS `my_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `my_db`;

DROP TABLE IF EXISTS `contact_acquisition`;
CREATE TABLE `contact_acquisition` (
  `SID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pilot` char(255) NOT NULL,
  `contact` char(255) NOT NULL,
  `email` char(255) NOT NULL,
  `tel` char(255) NOT NULL,
  `comments` char(255) NOT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8;

Before marking it as duplicate, please note:

This StackOverflow solution obviously doesn't fit: I am already adding CHARACTER SET utf8 to the LOAD DATA sql.

This other StackOverflow solution seems a bit far-fetched to me, since all the data I am inserting (the create database statement, the create table statement, the CSV, etc...) has been extracted from another server in which the exact same row is allowed. I have used the Mysql Workbench in order to extract creates/csv etc.

I am trying to export automatically from one server to another, so changing the data type in the destination is not an option for me.

Update:

This is the line (obfuscated, but keeping the non-alphanumerical characters):

64,xxxx xxxx,"xxxx xxxx, xxxx xxxx","[email protected], [email protected]",xxxx (xxxx) xxxx xxxx xxxx xxxx xxxx (xxxx),"xxxx xxxx xxxx'xxxx xxxx xxxx xxxx xxxx xxxx xxxx, xxxx xxxx xxxx'xxxx xxxx xxxx'xxxx xxxx xxxx xxxx xxxx"

The part MySQL complains about is: "xxxx xxxx xxxx'xxxx xxxx xxxx xxxx xxxx xxxx xxxx, xxxx xxxx xxxx'xxxx xxxx xxxx'xxxx xxxx xxxx xxxx xxxx". Which is wrapped by ", contains 1 comma and 3 '. Other lines (from the same CSV) that are correctly imported also have these characters. The length seems to be the only difference with other lines.

csv file is in utf8.

Upvotes: 1

Views: 2522

Answers (1)

TomNg
TomNg

Reputation: 1997

Try with LINES TERMINATED BY '\r\n'

LOAD DATA INFILE '(...)/contact_acquisition.csv'
INTO TABLE rs_ui_db.contact_acquisition
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

This works perfectly for me

Upvotes: 2

Related Questions