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?

Answer 1

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';