List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 27 2002 3:19pm
Subject:Re: Decimal and LOAD DATA
View as plain text  
At 15:18 +0100 12/27/02, Jonas C. Voss wrote:
>Hi all,
>
>I have a text-file containing products and their prices for a small 
>shop. I upload and insert the text-file into a MySQL table using the 
>LOAD DATA LOCAL INFILE command, and it really works a treat.
>
>My problem is that when inserting prices of more than one thousand, 
>the field it gets inserted into formats it wrong. Ex:
>
>The field "price" is of type decimal(13,2), inserting via LOAD DATA 
>etc. formats the price 1.139,00 to 1.13. I figured it had to do with 
>different delimiters of thousands and hundreds (the text-file uses 
>danish delimiters), so I cleaned up the file with PHP replacing the 
>. with a , in prices above 1000, and vice versa.
>
>This doesn't seem to fix the problem though. I can tell that the 
>replacing of ,'s and .'s is successfull (because I print the 
>resulting output to the screen), but MySQL still interprets the 
>number as 1.13, and not as 1.139,00 or 1,139.00 (yes, I tried both).
>
>Actually, I tried a plethora of formats including the following:
>
>1139
>113900
>1.139,00
>1,139.00
>1139,00
>1139.00
>
>But MySQL interprets them all as 1.13.

Based on that last statement, it sounds as though you're still loading
the original file and not the transformed version.  There is no way that
MySQL will interpret *all* those values as 1.13.

In any case, the values you end up loading should use '.' for the decimal
point, and should not contain any commas at all.

>
>Inserting '1139' via CLI or phpMyAdmin works as expected, I get a 
>price of '1139.00'.
>
>Is this an issue anybody dealt with before? I'd be happy to hear 
>your findings on the matter.
>
>The code I wrote for importing the text-file can be found here:
>
><http://xslt.it-c.dk/~jcv/usenet/tusindfryd.phps>

Looks like you upload a file to $upload_file, transform its contents
to $cleaned_file, and then attempt to use LOAD DATA with ... $upload_file!
(rather than the transformed file).

>
>The echo's are in danish, but you'll get the point of the script, hopefully.
>
>All thoughts on this appreciated.
>
>Thanks.
>
>specs:
>MySQL 3.23.53a-Max
>RH 7.3
>
>--
>// Jonas C. Voss // http://verture.net/ //

Thread
Decimal and LOAD DATAJonas C. Voss27 Dec
  • Re: Decimal and LOAD DATAPaul DuBois27 Dec
    • Re: Decimal and LOAD DATAJonas C. Voss27 Dec