Avatar of phpmysqlcoder
phpmysqlcoder
Flag for Denmark asked on

MySQL and REGEXP to handle digits with different decimal mark systems when loading from csv

I need to import csv files to mysql DB using "LOAD DATA LOCAL INFILE". The problem is that the csv files are generated by some machines which are using European decimal mark (comma)  and other machines which are using American decimal mark (dot). My database is using American system, so I need to be able to read from the csv and populate my database correctly.

here are all the possible cases

-----------------            -----------------------------
CSV                           desired result in the DB
-----------------            -----------------------------
  [empty]            ==> null
- [dash]              ==> null
100,000.00        ==>   100000.00
-100,000.00       ==> -100000.00
100.000,00        ==> 100000.00
100,000,000.00 ==> 100000000.00
100.000.000,00 ==> 100000000.00

Open in new window


I figured out that I could use Replace:

REPLACE(REPLACE("100.000.000,00", '.', ''), ',', '.')   ==> 100000000.00
REPLACE(REPLACE("100.000.000,00", ',', ''), '.', '.')   ==> 100000000.00

But I need to know the decimal mark to determine which replace to use.

The solution seems to use IF with regular expression REGEXP to determine the number format and then use the correct REPLACE


my incomplete sql query looks like this:
LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\TEMP\\data.csv'
INTO TABLE `test`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'
IGNORE 9 LINES
(@c1) 
set 
`budget`=IF(IFNULL(@c1), "")  = '-', null,REPLACE(REPLACE(@c1, '.', ''), ',', '.'));

Open in new window

Regular ExpressionsMySQL Server

Avatar of undefined
Last Comment
phpmysqlcoder

8/22/2022 - Mon
SANDY_SK

how about this

SELECT REPLACE( REPLACE( REPLACE('100.000.000,00', ',000','000') , '.000','000') , ',00' ,'.00')

First replace ',000' with '000'
Second replace '.000' with '000'
Third replace ',00' with '.00'
phpmysqlcoder

ASKER
The budget can be any number such as:

978.00
1.00
0.00
87.00
67.67
7.98
ASKER CERTIFIED SOLUTION
SANDY_SK

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
phpmysqlcoder

ASKER
Works nicely :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy