www.webdeveloper.com
Results 1 to 4 of 4

Thread: CSV import problem for comma as decimal point

  1. #1
    Join Date
    Aug 2008
    Posts
    6

    Red face CSV import problem for comma as decimal point

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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,639
    For a valid CSV formatting, if a field contains a comma then that field must be quoted.

    4 separate fields:
    Code:
    1,2,34,5
    2 fields with commas:
    Code:
    "1,2","34,5"
    3 fields, one with a comma:
    Code:
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2008
    Posts
    6

    Question

    Quote Originally Posted by NogDog View Post
    For a valid CSV formatting, if a field contains a comma then that field must be quoted.

    4 separate fields:
    Code:
    1,2,34,5
    2 fields with commas:
    Code:
    "1,2","34,5"
    3 fields, one with a comma:
    Code:
    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?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,639
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles