troubleshooting Question

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

Avatar of phpmysqlcoder
phpmysqlcoderFlag for Denmark asked on
MySQL ServerRegular Expressions
4 Comments1 Solution452 ViewsLast Modified:
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

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, '.', ''), ',', '.'));
ASKER CERTIFIED SOLUTION
SANDY_SK

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros