Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: CSV import into MySQL rounding up decimal values

    Hi there,

    I have searched the forum for this problem and did find a reference to it with an access database which didn't really address my issue but apologies if this has been covered elsewhere on the forum.

    I have been trying to import a csv (downloaded from PayPal of my yearly accounts to do my books) into a MySQL db i created. I managed the import fine however the values i.e the transaction amounts which have two decimal places are being rounded up or down when they are imported!

    I have set the field types for all the transaction amounts to decimal (19,4) as after research this seemed to be the way to go.

    Has anyone had this issue and solved it or knows what im doing wrong?

    Much appreciate any help.

    Cheers

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Not sure where your problem is. Can you provide some snippets of file upload (change amounts and names before posting) and also your load data command? I have run a test here and have not found any issue:

    Code:
    mysql> DROP TABLE IF EXISTS dectest;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE TABLE dectest(
        -> name VARCHAR(10),
        -> dist DECIMAL(10,2)
        -> );
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> 
    mysql> LOAD DATA INFILE 'test.txt'
        -> INTO TABLE dectest
        -> FIELDS TERMINATED BY ','
        -> LINES TERMINATED BY '\n'
        -> (name, dist);
    Query OK, 4 rows affected (0.01 sec)
    Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
    
    mysql> SELECT * FROM dectest;
    +------+-------+
    | name | dist  |
    +------+-------+
    | Bob  | 10.23 | 
    | Tom  |  9.57 | 
    | Eve  | 11.39 | 
    | Tim  | 63.21 | 
    +------+-------+
    4 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Sep 2012
    Posts
    2
    Hello,
    I have the similar problem when I work with load data from csv file
    In my case I have the next example
    Basic data:
    CSV-file - example.csv
    Code:
    1,1816.06
    2,398.99
    3,498.99
    4,439.39
    5,340
    6,270
    7,400.01
    8,200.06
    9,250.10
    SQL-query:
    Code:
    DROP TABLE IF EXISTS example;
    
    CREATE TABLE `example` (
    `id`  int(11) NOT NULL AUTO_INCREMENT ,
    `value`  float(15,4) NOT NULL DEFAULT 0.0000,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
    
    LOAD DATA INFILE "c:\\example.csv" IGNORE INTO TABLE `example`
    FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY ''
     (id,value);
    Results:
    Code:
    mysql> select * from example;
    +----+-----------+
    | id | value     |
    +----+-----------+
    |  1 | 1816.0601 |
    |  2 |  398.9900 |
    |  3 |  498.9900 |
    |  4 |  439.3900 |
    |  5 |  340.0000 |
    |  6 |  270.0000 |
    |  7 |  400.0100 |
    |  8 |  200.0600 |
    |  9 |  250.1000 |
    +----+-----------+
    9 rows in set (0.00 sec)
    How fix 1816.0601 to 1816.06???

    Also when I use:
    Code:
    `value`  float(15,8) NOT NULL DEFAULT 0.00000000
    then:
    Code:
    mysql> select * from example;
    +----+---------------+
    | id | value         |
    +----+---------------+
    |  1 | 1816.06005859 |
    |  2 |  398.98999023 |
    |  3 |  498.98999023 |
    |  4 |  439.39001465 |
    |  5 |  340.00000000 |
    |  6 |  270.00000000 |
    |  7 |  400.01000977 |
    |  8 |  200.05999756 |
    |  9 |  250.10000610 |
    +----+---------------+
    9 rows in set (0.00 sec)
    May be someone know what I should do for saving correctly data as they were in initial file?
    Attached Files Attached Files
    Last edited by madman25; 09-02-12 at 16:36. Reason: add files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madman25 View Post
    May be someone know what I should do for saving correctly data as they were in initial file?
    use DECIMAL

    FLOAT is an approximate numeric type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2012
    Posts
    2
    Thank you, this is resolve my problem

Posting Permissions

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