glly glly - 2 months ago 6
Perl Question

Remove commas from numeric fields of a CSV file

I have a input file like the following:

13/9/16,"71,150","49,122","13,212","3,135","3,652","140,271",0,Pull,301 Off,Standard,0,"6,237",43,53,0,"6,141","3,641","2,500","25,353","4,136","4,004",335,89,"1,909",312,382,49,183,0,"44,778"
14/9/16,"72,799","53,420","13,130","3,359","3,798","146,506",0,Pull,301 Off,Standard,0,"6,779",45,56,0,"6,678","4,178","2,500","25,940","4,232","3,979",349,96,"1,953",319,409,51,190,0,"46,525"
15/9/16,"97,532","16,861","12,386","6,066","17,323","150,168",F&F,+24,301 On,Standard,0,"2,413",203,102,0,"2,108","1,054","1,054","34,753","5,670","3,754","1,588",172,"2,616",427,739,231,867,0,"49,546"

I am trying to delete the commas
in strings that start and end with double-quote

So for example line 1 has the string
I would like that to look like
with the rest of line 1 around it.

so the complete output for line 1 would look like the following:

13/9/16,"71150","49122","13212","3135","3652","140271",0,Pull,301 Off,Standard,0,"6237",43,53,0,"6141","3641","2500","25353","4136","4004",335,89,"1909",312,382,49,183,0,"44778"

I was thinking of something like


but that will replace all of the commas in the entire file.

I know I can use the following line of code to find the strings that I want to change:


but I don't know where to go from there.

perl -pe 's/("\d+),(\d+")/\1\2/g' infile.txt
  • "\d+ double quote followed by one or more digits
  • , match a comma
  • \d+" one or more digits followed by double quote
  • () grouping used to capture matched pattern
  • \1 first captured group
  • \2 second captured group

If you want to change the file inplace, use

perl -i.bkp -pe 's/("\d+),(\d+")/\1\2/g' infile.txt

which will change infile.txt as well create a backup of original file as infile.txt.bkp

Further Reading: