[MDEV-5057] Importing number from CSV, scientific notation with comma instead of period doesn't work Created: 2013-09-23  Updated: 2013-11-22  Resolved: 2013-09-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.32-galera
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Christiaan Mulder Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
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.



 Comments   
Comment by Sergei Golubchik [ 2013-09-23 ]

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, ',','.');

Comment by Christiaan Mulder [ 2013-09-23 ]

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

Comment by Sergei Golubchik [ 2013-11-22 ]

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.

Generated at Thu Feb 08 07:01:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.