Decimal and LOAD DATA
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
Re: Decimal and LOAD DATA
On fredag, dec 27, 2002, at 16:19 Europe/Copenhagen, Paul DuBois wrote: At 15:18 +0100 12/27/02, Jonas C. Voss wrote: 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. Ok, thanks. 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). Blast, you are right! Well, sort of. I replace the content of $uploadfile with $cleaned_file here: # truncates the file pointer: ftruncate($fd,0); # writes content of $cleaned_file to file pointer $fd: fwrite($fd, $cleaned_file); But, I didn't put a + in my file pointer, so the file was only readable, not writeable, see? $fd = fopen ($uploadfile, "r"); Egad I feel stupid. Thanks for pointing me in the right direction, it works perfectly now. :) -- // 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
