load data local infile with decimal values

Question

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.

possible duplicate of [Change decimal separator in MySQL](http://stackoverflow.com/questions/8669212/change-decimal-separator-in-mysql) — Barmar, Feb 11 '14 at 08:59
lol yeah, i will say that to customer :D the problem is that customer generates the .csv file in this format and he wants to upload it in that way — enigmaticus, Feb 11 '14 at 09:03
why not? anyway the customer will be paying you for the solution of this problem. Replace-all is the cheapest solution you can get — WeMakeSoftware, Feb 11 '14 at 09:05
ha ha you got to be joking me :D this is not constructive answer :) — enigmaticus, Feb 11 '14 at 09:06
Not joking at all. From your post you're working with MySQL DB. For me that means that your app is not the one of high_level_enterprise_solution_for_customers_which_allows_everything_to_be_done. So replace-all is the way to go. You can name it differently like Adapting / Converting the input file before importing to the application. But that does not change anything — WeMakeSoftware, Feb 11 '14 at 09:10

score 7 · Accepted Answer · answered Feb 11 '14 at 09:11

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, ',', '.');

load data local infile with decimal values

1 Answers1