CODEWITHSUNDEEP
mysqlcsv
enigmaticus
enigmaticus

Reputation: 548

load data local infile with decimal values

I made a importer for the csv files for the customer and the problem which I have is that I get truncated values for decimal values in the file, example:

in .csv file price is 12,23
and in database it is saved as 12.

I checked the fields, they are set to decimal(10,2) so this shouldnt be usual. My code for loading file is:

LOAD DATA LOCAL INFILE "U:/v7/root/file/docimport/3/87/article.csv" 
INTO TABLE im_artikel 
CHARACTER SET latin1 FIELDS TERMINATED BY ";" IGNORE 1 LINES 
(article,price,grossprice);
show WARNINGS;

And I get an error:

1265 Data truncated for column 'price' at row 1
1265 Data truncated for column 'grossprice' at row 1

.Csv file looks like this:

Shoes;40,00;21,46

I could change all the fields to varchar than I dont have problems, but I think this is not the solution.

Upvotes: 3

Views: 6843

Answers (1)

fancyPants
fancyPants

Reputation: 51888

Please have a try with this:

LOAD DATA LOCAL INFILE "U:/v7/root/file/docimport/3/87/article.csv" 
INTO TABLE im_artikel 
CHARACTER SET latin1 FIELDS TERMINATED BY ";" IGNORE 1 LINES 
(article,@p,@gp)
SET price = REPLACE(@p, ',', '.'), grossprice = REPLACE(@gp, ',', '.');

Upvotes: 7

Related Questions