CSV import into MySQL rounding up decimal values

OscarTT21 · #1 (**permalink**) 01-25-12, 04:43

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

it-iss.com · #2 (**permalink**) 01-25-12, 07:33

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech