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

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