load data local infile with decimal values
csv, mysql
Solution
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, ',', '.');
Problem
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.