HeidiSQL: Import CSV

Question

A few lines of my CSV:

"id","date","date_","pow","ene","reac"
"ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43"
"ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43"
"ES0134000563224539AM0F","2013-05-02","2013-06-05","62","43","43"

I tried to import text file but I've received an error message with code 1261. I used the following control characters:

• Fields terminated by: "
• Fields enclosed by: ,
• Fields escaped by: "
• Lines terminated by: \r\n

Answer 1

As per documentation your error is:

Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns

Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition if below command doesn't work)

Try this:

LOAD DATA INFILE 'path/to/file' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id,date,date_,pow,ene,reac);

If you are interested you might want to refer this blog post which explains various use-cases for loading delimited data to mysql.

Answer 2

Thanks @mysql_user and @oNare for your answers and I do it:

• Fields terminated by: ,
• Fields enclosed by: "
• Lines terminated by: \r\n

When I tried to do that with MySQL Workbench 6.3 and Table Data Import Wizard, I couldn't believe it because it is so slow. And I tried to do it with HeidiSQL and is faster than MySQL Workbench but no enough. My CSV is a big file (59GB) and the problem is the time to load those data.

Sorry for my English. It is not my native language. Thanks.