List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 17 2000 2:33am
Subject:Re: type MONEY, and parsing MONEY fields
View as plain text  
Hi!

>>>>> "sasha" == sasha  <sasha@stripped> writes:

sasha> Philip Brown wrote:
>> 
>> [ sasha@stripped writes ]
>> > >...
>> > >   Finally, I looked at the raw data:
>> > >
>> > >   "2,1234.00"
>> > >
>> > >   was being read in as "2.00" !!!!
>> > >
>> > >   Could you please fix this?
>> >
>> > How is your file delimited? If it is comma-delimited, there is no way to
>> > fix this other than you fixing the file itself - how will you know that
>> > the ','in
>> > the money is not a field delimiter? If it is delimited with something else,
>> > you must specify this as option to LOAD DATA INFILE
>> 
>> here is the exact load line:
>> 
>> load data infile '/tmp/sunprices' into table sunprices fields terminated by
>> '|';
>> 
>> So as you can see, this data is NOT comma-delimited.
>> 
>> When I turned the field into "varchar(12)",THEN it read in as expected.
>> (and as you might expect, still had the comma in the field)

sasha> Ok, I see what the problem is. Our conversion routine from string to float need
sasha> to be slightly modified to just ignore commas. This should not be a very
sasha> difficult fix.

I don't know if its right to fix this!  If we do, we will get different
behaveour depending on the type of delimeter the user have used. We
will also get problems that in some languages ',' is used to separate
the integer part from the decimals.

I think it's better to give warnings and force the user to have the
text file in a proper format than to silently remove ',' !

(We could of course add even more options to LOAD DATA INFILE to tell
it the format of the numbers, but this is another issue...)

Regards,
Monty
Thread
type MONEY, and parsing MONEY fields(Philip Brown)17 Mar
  • Re: type MONEY, and parsing MONEY fieldssasha17 Mar
    • Re: type MONEY, and parsing MONEY fields(Philip Brown)17 Mar
      • Re: type MONEY, and parsing MONEY fieldssasha17 Mar
        • RE: type MONEY, and parsing MONEY fieldsindrek siitan17 Mar
        • Re: type MONEY, and parsing MONEY fieldsMichael Widenius17 Mar
          • Warnings from LOAD DATA INFILETim Bunce19 Mar
            • Re: Warnings from LOAD DATA INFILEsinisa19 Mar
            • Warnings from LOAD DATA INFILEMichael Widenius20 Mar
              • Re: Warnings from LOAD DATA INFILETim Bunce20 Mar
                • Re: Warnings from LOAD DATA INFILEMichael Widenius21 Mar
                  • Re: Warnings from LOAD DATA INFILETim Bunce21 Mar
                    • Re: Warnings from LOAD DATA INFILETim Bunce21 Mar
              • Re: Warnings from LOAD DATA INFILETim Bunce17 Apr
                • Re: Warnings from LOAD DATA INFILEMichael Widenius23 Apr
        • Re: type MONEY, and parsing MONEY fieldsVivek Khera17 Mar