HeidiSQL – How to Import CSV

csvimportinnodbMySQLmysql-5.6

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

Best Answer

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.

Related Question