Complex regex to replace commas with dots for quoted values then remove quotes

Question

I have some csv files which have been incorrectly exported. Some fields containing numbers have been incorrectly exported as Strings with the decimal place as a comma instead of a dot.

Here's a couple of examples:

"GREEN,"15,4",55,"15,5",64,"17,0","18,9",107,145,21,38, "BLUE","15,1",46,"15,2",51,"11,4","13,5",89,96,25,7,

So, I want to switch the commas to dots for numbers in quotation marks and then remove the quotation marks from all numbers.

Can anyone help me out here please?

If you show you've made an attempt to solve this yourself you'll find people will be much more generous in helping you — fugu, Jan 04 '17 at 09:31
Your example is **confusing** which numbers are surrounded by quotations ? — Meninx - メネンックス, Jan 04 '17 at 09:34
@Meninx I guess the values like `"15,4"` should become `15.4` without the quotation marks. — michaPau, Jan 04 '17 at 10:20

Sobrique · Accepted Answer · 2017-01-04T10:02:06.777

4

Use a CSV parser, and this becomes easy:

#!/usr/bin/perl
use warnings;
use strict;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new( { binary => 1, auto_diag => 1 } );

while ( my $row = $csv->getline( \*DATA ) ) {
   s/,/./ for @$row;
   $csv->say( \*STDOUT, $row );
}


__DATA__
"GREEN","15,4",55,"15,5",64,"17,0","18,9",107,145,21,38,
"BLUE","15,1",46,"15,2",51,"11,4","13,5",89,96,25,7,

Example above uses inlined data to make it runnable standalone - you'll probably want to use an input and output file instead of DATA and STDOUT.

If you're particularly wanting to keep the quotes around the 'strings' but not the 'numbers', then you can make use of keep_meta_info or just re-add them when they're needed.

edited Jan 04 '17 at 10:02

answered Jan 04 '17 at 09:47

Sobrique

52,974
7
60
101

Why the downvote? This is the right solution! – Toto Jan 04 '17 at 10:46
Maybe cos the regex is too generic and could match a legitimate string? – Chris Turner Jan 04 '17 at 13:34

score 2 · Answer 2 · answered Jan 04 '17 at 09:42

2

,(?!(?:[^"]*"[^"]*")*[^"]*$)

You can use this remove , between ".See demo.

https://regex101.com/r/t5Euq1/1

answered Jan 04 '17 at 09:42

vks

67,027
10
91
124

Why downvoted ? – vks Jan 04 '17 at 11:32
1

Thanks for the link - awesome site ;o). I even did not have the privilege to get the quotes around the digits , thus https://regex101.com/r/t5Euq1/6 `([;]?["]*[0-9]+)[,]([0-9]+["]*[;]?)` – Yordan Georgiev Dec 28 '19 at 07:44

Complex regex to replace commas with dots for quoted values then remove quotes

2 Answers2