Remove only the commas present within double-quoted numbers in a csv file

Question

In a text file, I want to remove , (commas) and also the " (quotes) (only if the double quotes contains numbers separated by commas).

56,72,"12,34,54",x,y,"foo,a,b,bar"

Expected ouput

56,72,123454,x,y,"foo,a,b,bar"

Note: I show the above line just as an example. My text file contains many lines like above and the numbers separated by commas present within the double quotes should vary. That is,

56,72,"12,34,54",x,y,"foo,a,b,bar"
56,92,"12,34",x,y,"foo,a,b,bar"
56,72,"12,34,54,78,76,54,67",x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar","12,34,54"
56,72,x,y,"foo,a,b,bar","12,34,54","45,57,84,92","bar,foo"

Expected output:

56,72,123454,x,y,"foo,a,b,bar"
56,92,1234,x,y,"foo,a,b,bar"
56,72,12345478765467,x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar",123454
56,72,x,y,"foo,a,b,bar",123454,45578492,"bar,foo"

There a n number of numbers present within the double quotes separated by commas. And also leave the double quotes which contains characters as it is.

I love sed text processing tool. I'm happy if you post any sed solution for this.

Answer 1

If perl is OK, here is a short (and probably fast, if not necessarily simple :) ) way of doing it:

perl -pe 's:"(\d[\d,]+)":$1=~y/,//dr:eg' file

The e flag to the s::: operator (which is just another way of writing s///) causes the replacement to be treated as an expression which is evaluated every time. That expression takes the $1 capture from the regex (which is already missing the quotes) and translates (y///, which can also be written as tr///) it by deleting (/d) all the commas. The r flag to y is necessary in order to get the value to be the translated string, instead of the count of translations.

For those who somehow feel sullied by perl, here is the python equivalent. Python is really not a shell one-liner tool, but sometimes it can be cajoled into co-operating. The following can be written as one line (unlike for loops, which cannot be), but the horizontal scrolling makes it (even more) unreadable:

python -c '
import re;
import sys;
r=re.compile("\"(\d+(,\d+)*)\"");
all(not sys.stdout.write(r.sub(lambda m:m.group(1).replace(",",""),l))
    for l in sys.stdin)
' < file

Answer 2

This (adapted from here) should do what you need though @rici's Perl one is much simpler:

$ sed -r ':a;s/(("[0-9,]*",?)*"[0-9,]*),/\1/;ta; s/""/","/g; 
          s/"([0-9]*)",?/\1,/g ' file
56,72,123454,x,y,"foo,a,b,bar"
56,92,1234,x,y,"foo,a,b,bar"
56,72,12345478765467,x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar",123454,
56,72,x,y,"foo,a,b,bar",123454,45578492,"bar,foo"

Explanation

• :a : define a label called a.
• s/(("[0-9,]*",?)*"[0-9,]*),/\1/ : This one needs to be broken down
  • First of all, using this construct : (foo(bar)), \1 will be foobar and \2 will be bar.
  • "[0-9,]*",? : match 0 or more of 0-9 or ,, followed by 0 or 1 ,.
  • ("[0-9,]*",?)* : match 0 or more of the above.
  • "[0-9,]* : match 0 or more of 0-9 or , that come right after a "
• ta; : go back to the label a and run again if the substitution was successful.
• s/""/","/g; : post-processing. Replace "" with ",".
• s/"([0-9]*)",?/\1,/g : remove all quotes around numbers.

This might be easier to understand with another example:

$ echo '"1,2,3,4"' | sed -nr ':a;s/(("[0-9,]*",?)*"[0-9,]*),/\1/;p;ta;'
"1,2,34"
"1,234"
"1234"
"1234"

So, while you can find a number that is right after a quote and followed by a comma and another number, join the two numbers together and repeat the process until it is no longer possible.

At this point I believe it is useful to mention a quote from info sed that appears in the section describing advanced functions such as the label used above (thanks for finding if @Braiam):

In most cases, use of these commands indicates that you are probably better off programming in something like `awk' or Perl.

Answer 3

For CSV data, I'd use a language with a real CSV parser. For example with Ruby:

ruby -rcsv -pe '
  row = CSV::parse_line($_).map {|e| e.delete!(",") if e =~ /^[\d,]+$/; e} 
  $_  = CSV::generate_line(row)
' <<END
56,72,"12,34,54",x,y,"foo,a,b,bar"
56,92,"12,34",x,y,"foo,a,b,bar"
56,72,"12,34,54,78,76,54,67",x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar","12,34,54"
56,72,x,y,"foo,a,b,bar","12,34,54","45,57,84,92","bar,foo"
END

56,72,123454,x,y,"foo,a,b,bar"
56,92,1234,x,y,"foo,a,b,bar"
56,72,12345478765467,x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar",123454
56,72,x,y,"foo,a,b,bar",123454,45578492,"bar,foo"

Answer 4

Assuming that the last record in the example data in the question is a typo as it has two fields too many compared to the other records, one may use Miller (mlr) to delete commas from any field that is a string containing nothing other than digits and commas:

$ mlr --csv -N put 'for (k,v in $*) { if (is_string(v) && v !=~ "[^,[:digit:]]") { $[k] = gsub(v,",","") } }' file
56,72,123454,x,y,"foo,a,b,bar"
56,92,1234,x,y,"foo,a,b,bar"
56,72,12345478765467,x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar",123454

This treats the input as header-less CSV and then iterates over all fields of each record, testing each string field for any other character than commas and digits. If there isn't anything other than commas and digits in the field, all commas are deleted using gsub().

Nicely formatted command:

mlr --csv -N put '
    for (k,v in $*) {
        if (is_string(v) && v !=~ "[^,[:digit:]]") {
            $[k] = gsub(v,",","")
        }
    }' file

Answer 5

Using Raku (formerly known as Perl_6)

~$ raku -pe 's:g/ \" ~ \" (\d+) ** 2..* % "," /{$0.join}/;'  file

Sample Input:

56,72,"12,34,54",x,y,"foo,a,b,bar"
56,92,"12,34",x,y,"foo,a,b,bar"
56,72,"12,34,54,78,76,54,67",x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar","12,34,54"
56,72,x,y,"foo,a,b,bar","12,34,54","45,57,84,92","bar,foo"

Sample Output:

56,72,123454,x,y,"foo,a,b,bar"
56,92,1234,x,y,"foo,a,b,bar"
56,72,12345478765467,x,y,"foo,a,b,bar"
56,72,x,y,"foo,a,b,bar",123454
56,72,x,y,"foo,a,b,bar",123454,45578492,"bar,foo"

Raku is a programming language in the Perl-family with a number of powerful regex features. See the URL below for a general overview of this answer:

https://unix.stackexchange.com/a/722570/227738

In the code above, digits are recognized and embedded commas are removed. The regex takes advantage of the fact that nested structures can be denoted with Raku's new ~ tilde (nested) notation, such that \" ~ \" [\d+] means "one-or-more digit(s) surrounded by " double-quotes".

Additionally, repeated structures can be denoted with Raku's new % modified quantifier for repeated structures. The notation [\d+] ** 2..* % "," means "one-or-more digit(s) separated by , commas with this pattern repeated ** 2..* two or more times. [If there happens to be a trailing separator (e.g. comma), use a %% instead of % in the syntax].

This is just a start. CSV files with alternate separators, embedded newlines, embedded commas, potentially blank fields, etc., really need to be handled by a true CSV parser like Raku's Text::CSV module. See the links below for details.

https://docs.raku.org/language/regexes
https://raku.land/github:Tux/Text::CSV
https://raku.org

Answer 6

Blockquote

Hi Here is the Python code to Replace commas with in double quotes, commas are replaced with pipe (|) character

This Python code is to replace commas enclosed in double quotes

eg: x,y,z,1,2,"r,e,t,y",h,8,5,6

if replace with Pipe x,y,z,1,2,"r|e|t|y",h,8,5,6

if replace with null x,y,z,1,2,"rety",h,8,5,6

writingFile = open('FileToWrite', 'w')
with open('FileToRead') as f:

    while True:

        c = f.read(1)
        if not c:
            print ("End of file")
            break
        print ("Read a character:", c)


        if c=='"':
            writingFile.write(c) 
            c = f.read(1)
            while c != '"':
                if c== ',':
                    c= '|'
                writingFile.write(c)
                c = f.read(1)


        writingFile.write(c)


writingFile.close()