Decimal and LOAD DATA

[Jonas C. Voss]

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.

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>

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/ //


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php