1

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
3
  • Try no enclosing and terminating with comma, I would also check all the columns have complete data.
    – Nelson
    Jul 30, 2015 at 11:19
  • Your fields are terminated by , not by ".
    – oNare
    Jul 30, 2015 at 12:51
  • And enclosed by ". It is unclear whether "escaped" is set correctly.
    – Rick James
    Aug 1, 2015 at 0:11

2 Answers 2

2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.