CSV import problem for comma as decimal point

[coldfire7]

When I import a table with a decimal value which is using "comma as separating point" like,

1,2332123 or 31,93

it converts them to

1,0000000 or 31,00

after importing.

How to solve this ? I figure I have to format the import data but dont know about decimal fields!!!

[NogDog]

For a valid CSV formatting, if a field contains a comma then that field must be quoted.

4 separate fields:

1,2,34,5

2 fields with commas:

"1,2","34,5"

3 fields, one with a comma:

1,"2,34",5

If your CSV file is not properly handling literal comma characters like that, it will be very difficult to handle it correctly.

If it is correctly formatted, then we may need to know more about how you're doing the import to make sure it understands how to parse CSV files.

[coldfire7]

For a valid CSV formatting, if a field contains a comma then that field must be quoted.

4 separate fields:

1,2,34,5

2 fields with commas:

"1,2","34,5"

3 fields, one with a comma:

1,"2,34",5

If your CSV file is not properly handling literal comma characters like that, it will be very difficult to handle it correctly.

If it is correctly formatted, then we may need to know more about how you're doing the import to make sure it understands how to parse CSV files.

Here are the details, table has fields of Longitude & Latitude and their types are decimal(23,20) for both.

Now, values for this table is stored in the .txt file as, (below is the first row of values for the three fields in the table)

"postalcode";"latitude";"longitude"
"1020";"50,8804058";"4,355464254"

Now, when I try to Import in phpmyadmin with Characterset = utf-8
with Format of Imported file
as

Fields terminated by= ;
Fields enclosed by= "
Fields escaped by= \
Lines terminated by= auto

After successful Import, it stores the values as

postalcode | latitude | longitude
1020 | 50.00000000000000000000 | 4.00000000000000000000

20 Zeros after decimal are because of decimal(23,20)

suggestions?

[NogDog]

Ah...I wasn't even thinking about the usage of comma as a decimal point. As far as I know, MySQL only recognizes the period (full stop) for that when specifying a float/decimal literal. I don't know if other DBMS's may provide a locale setting or some other way that would allow use of the comma for that. I suppose I'd probably throw together a script that would read the file and convert the comma into those fields to a period.