Importing Data from CSV file into MySQL, Scientific Notation

Question

I am using a LOAD DATA INFILE to read from a CSV and insert into a MySQL table. The problem is that one column in the CSV is in scientific notation and when it is read and loaded into the table it won't go into a column of type DOUBLE, FLOAT, or REAL. Is there a solution to this?

114,2015-05-11 00:00:00.000,0,100,14,2.576927E+10,1.730594E+10,1.904524E+10,1.788426E+10,69.40149

In this one row of the CSV are the scientific notation values that when loaded via LOAD DATA INFILE just become 100000

perhaps `http://stackoverflow.com/questions/6401145/how-do-you-store-scientific-notation-numbers-in-mysql` — Marichyasana, Jul 21 '15 at 19:08
My problem isn't storing the information per say, my problem is getting it from the CSV to the database. The scientific notation messes this up — Ben Howells, Jul 21 '15 at 19:12
I'm not sure if they will need any use for math but I was trying to keep that option open — Ben Howells, Jul 21 '15 at 20:01
Enclosed by is added. At this point I think I will just load the column as a VARCHAR. I don't believe any calculations will be made with it — Ben Howells, Jul 22 '15 at 17:53

score 0 · Answer 1 · answered Apr 14 '22 at 13:18

0

load DATA local infile yourfile INTO TABLE Direction_Prix_proxi.tmp_prix_conc FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (@yourcolumn) set yourcolumn = REPLACE(@yourcolumn, ',', '.')

answered Apr 14 '22 at 13:18

Malou

141
8

Importing Data from CSV file into MySQL, Scientific Notation

1 Answers1

Linked