Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.32-galera
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:Windows, working with HeidiSQL
Description
I'm importing millions of numbers into a database using:
LOAD DATA LOCAL INFILE 'filename' INTO Table 'tablename' Fields terminated by ';' Lines terminated by '\r';
The numbers are mostly formatted like: 8,70840419842013E-03.
I am importing into a 'FLOAT not null' field. The database converts the input to '8' instead of '0.00870840419842013'.
New to MariaDB (and creating issues on this board) so please be gentle or give me tips on how to improve this issue. If this should be filed differently, please let me know.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi Sergei,
For number not using scientific notation, MariaDB works fine with comma as a decimal separator. That's what made me make a bug report.
Your view on that?
Regards,
Christiaan
I cannot repeat it. I tried to load numbers in scientific and not scientific notation, with commas and with dots, using your exact LOAD DATA LOCAL INFILE statement. In all cases dots worked as decimal separators and commas never did.
This is not a bug, MariaDB uses a dot (.), not comma (,) as a decimal separator.
If you'd like to import your file, you can do it with (let's assume, the table has three columns, and the third one is your FLOAT):
LOAD DATA LOCAL INFILE 'filename'
INTO Table 'tablename' (col1, col2, @var1)
Fields terminated by ';'
Lines terminated by '\r'
SET col3=REPLACE(@var1, ',','.');