Nikanor Nikanor - 25 days ago 8
MySQL Question

Importing csv file into mysql: Incorrect decimal value: '"0.5'

Can someone point me what I am doing wrong:
csv file:

weight,student,price
0.5,4,2.3


command:

LOAD DATA INFILE 'prices.csv'
INTO TABLE prices
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;


Error:

Incorrect decimal value: '"0.5' for column 'weight' at row 1

Answer

There might be a enclosing (or escaping) character in your file (I don't know your real file). Try to specify ENCLOSED BY

LOAD DATA INFILE 'prices.csv'
INTO TABLE prices
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

If that doesn't work, try it with ESCAPED BY too.

LOAD DATA INFILE 'prices.csv'
INTO TABLE prices
FIELDS TERMINATED BY ',' ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
Comments