0

I have a problem with the importing of a CSV file to my table in MySQL. The values (3) of the columns (var1, var2, dates) are not loading, as shown in the image below:

enter image description here

First I created the table,

CREATE TABLE table1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,
    var1 INT NOT NULL,
    var2 INT NOT NULL,
    dates DATE NOT NULL
);

After that, I imported the data to the table:

LOAD DATA LOCAL INFILE '/home/task.csv' 
INTO TABLE table1 
FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

I am using an Ubuntu terminal.

  • 1
    Is there other data in the file that is being imported? (you specify three columns, but the table only has 4 columns, and you wouldn't expect id to come from the file. Some people pare out information to simplify a problem or to eliminate confidential info; sometimes, that "simplifies" critical info out of the question). Also, you have double-checked that the file is formatted as you expect, and that the line termination characters are what you expect? Are you getting as many rows with the zero values shown as are supposed to be in the file? – RDFozz Mar 21 '18 at 23:53
  • Yes, it is just 4 columns the id is appearing normally, Yes the file is formatted I double checked it. It is just showing zeros is not showing the real values. - ........"the line termination characters are what you expect?".....this I do not know. – Yan Mar 22 '18 at 0:02
  • 1
    this refers to the fact that EOL on Windows is two characters, ASCII 13 and 10 (carriage return and line feed, CRLF, etc.) As I recall, Unix systems (including Linux) use ASCII 13 only. You specified the line ending as \n - if this file was generated on a Windows system, the terminator should probably be \n\r (as best I recall). – RDFozz Mar 22 '18 at 0:11
0

Specify the column list so you won't include id. Add this after then IGNORE clause:

(var1, var2, dates)

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.