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

Barmar
– Barmar

2014-02-11 08:59:30 +00:00
Commented Feb 11, 2014 at 8:59 — Barmar
– Barmar, Commented Feb 11, 2014 at 8:59
MySQL uses . as the decimal separator, not ,.

Barmar
– Barmar

2014-02-11 09:00:01 +00:00
Commented Feb 11, 2014 at 9:00 — Barmar
– Barmar, Commented Feb 11, 2014 at 9:00
I dont feel good with changing data for 60k records :/

enigmaticus
– enigmaticus

2014-02-11 09:00:50 +00:00
Commented Feb 11, 2014 at 9:00 — enigmaticus
– enigmaticus, Commented Feb 11, 2014 at 9:00
Well, that's just too bad.

Barmar
– Barmar

2014-02-11 09:01:28 +00:00
Commented Feb 11, 2014 at 9:01 — Barmar
– Barmar, Commented Feb 11, 2014 at 9:01
just open it up in a text editor and run replace-all? :)

WeMakeSoftware
– WeMakeSoftware

2014-02-11 09:02:44 +00:00
Commented Feb 11, 2014 at 9:02 — WeMakeSoftware
– WeMakeSoftware, Commented Feb 11, 2014 at 9:02

fancyPants · Accepted Answer · 2014-02-11 09:11:39Z

7

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

answered Feb 11, 2014 at 9:11

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

load data local infile with decimal values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related