mysql [Row 1 was truncated; it contained more data than there were input columns] ERROR

Question

my text file is like this. https://drive.google.com/open?id=1faW_OkO7_VoEQL_gndFIOrJv2e4Ycuzo

and my table is here.

CREATE TABLE news(
    num INT auto_increment primary key,
    link VARCHAR(150),
    date INT,
    title VARCHAR(150) unique,
    description TEXT 
);

i try

LOAD DATA INFILE 'test.txt'
    INTO TABLE news
    CHARACTER SET utf8mb4
    FIELDS
    TERMINATED BY ', ' OPTIONALLY ENCLOSED BY '"' 
    LINES
    TERMINATED BY '\n' (link, date, title, description);

but it not working what is my mistake?

@Madhur Bhaiya oh... how can i start at (link , date, title, description) ? num is auto_increment field so i don't want write it at my text — sang oh, Sep 22 '18 at 10:00
@scaisEdge it same results that i use 'LONGTEXT' type on description.. — sang oh, Sep 22 '18 at 10:04
@sangoh you have double quotes within the string for `description` . eg: `"description": "과기` While you are mentioning to optionally enclose by `'"'` — Madhur Bhaiya, Sep 22 '18 at 10:18

score 1 · Accepted Answer · answered Sep 22 '18 at 13:02

Your input file has four columns looking like this

url,month,title,"description": data

A CSV import will include the word "description": at the beginning of each description column.

So remove OPTIONALLY ENCLOSED BY '"' from your command, and you should be all set.

By the way, make sure your table definition mentions the utf8mb4 character set (or some characters from your input may not get represented correctly). For best results, don't rely on the server defaults to choose character set.

CREATE TABLE news(
            num INT auto_increment primary key,
           link VARCHAR(150),
           date INT,
           title VARCHAR(150) unique,
    description TEXT 
)
COLLATE 'utfmb4_general_ci';

"description": is my fault. i will remove this.!! thanks. i'm normalization description field with no special character so utfmb4 don't need to me! — sang oh, Sep 22 '18 at 13:38

score 0 · Answer 2 · edited Nov 24 '18 at 09:00

This error is because of Optionally Enclosed by '. I have faced the same issue before and got resolved it by making a simple adjustment. Just use the Enclosed by clause immediately after 'Fields' keyword.
Hence your code would be like something:

LOAD DATA INFILE 'test.txt'
INTO TABLE news
CHARACTER SET utf8mb4
FIELDS OPTIONALLY ENCLOSED BY '"' 
TERMINATED BY ', ' 
LINES
TERMINATED BY '\n' (link, date, title, description);

mysql [Row 1 was truncated; it contained more data than there were input columns] ERROR

2 Answers2