Regular expression, replace "22,09" with "22.09"

I am importing a dataset in csv forma开发者_运维技巧t to a database.

The structure of the csv files are like this

Part number, Description, Netto price, Brutto price, comment

11009, Ball Bearing, "22,09", "38,05", "Note, this article is the same as koyo xxxxxx"

As I am not yet familiar with regular expressions, can someone please put help me?

Or maybe a write perl script for me.

Thank you

There's no need for that; it can be done in MySQL alone.

LOAD DATA INFILE 'data.csv'
INTO TABLE prices
FIELDS
  TERMINATED BY ','
  ENCLOSED BY '"'
(partno, desc, @net, @gross, comment)
SET
  net=REPLACE(@net, ',', '.'),
  gross=REPLACE(@gross, ',', '.'),

If you can handle using Vim (here's a quickstart) to edit your CSV files, this command in Vim would give you the behavior you want, assuming inputs are formed as such ("xx,yy" to "xx.yy"):

:%s/"\(\d\+\),\(\d\+\)"/"\1.\2"/g

You can then hit :wq to save your file and exit the program. That regex (/"(\d+),(\d+)"/) should also serve as a starting point in other programs (or languages) that accept regular expressions.

Here's how you would do it in the Ruby programming language. Not sure if this helps:

row = 'abc, "22,09", t'
row.gsub(/([0-9]+),([0-9]+)/, '\1.\2')
puts row       # result is: abc, "22.09", t

The call to gsub searches for any series of digits followed by a comma followed by any series of digits, and replaces the comma with a period.

So, you want to replace commas only when float numbers are in a string delimited by double quotes. This is the regexp you want :

/(?<=")(\d+),(\d+)(?=")/

Which would only match {22 ; 09} and {38 ; 05}.