How to remove comma from a csv file with quotes?

Question

My Input is:

Name, Country, City
Jason, US, Memphis, "1,000"
David, US, Little Rock, "8,765,453"
"Karam, Sage", US, Nazareth, "4,678"
"David, simon", US, Chicago, "1,234"

I want output as:

Name, Country, City
Jason, US, Memphis, "1000"
David, US, Little Rock, "8765453"
"Karam, Sage", US, Nazareth, "4678"
"David, simon", US, Chicago, "1234"

I want the comma to be removed from only 4th column and not first column.

Answer 1

Or with perl:

perl -pe 's/(\d),(\d)/$1$2/g' infile.txt

which just removes all commas that are surrounded by digits (\d).

I assumed that removing thousand separators was the essence in your task - not in which column the commas are located.

Answer 2

Assuming the 4^th filed is your last filed, then an AWK approach would be.

awk -F\" '{gsub(",","",$(NF-1))}1' OFS=\" infile.txt

The output is:

Name, Country, City
Jason, US, Memphis, "1000"
David, US, Little Rock, "8765453"
"Karam, Sage", US, Nazareth, "4678"
"David, simon", US, Chicago, "1234"

Answer 3

With sed:

sed -e ':a' -e 's/,\([^"]*"$\)/\1/;ta' infile.txt

You loop as long as there is a comma to be removed after the next-to-last double quote.

Answer 4

I recommend you use a CSV parser for CSV data: ruby has one

ruby -rcsv -e '
  data = CSV.read(ARGV.shift, :col_sep => ", ")
  out = CSV.new($stdout, :col_sep => ", ")
  data.each {|row| row[-1].delete! ","; out << row}
' file.csv

Name, Country, City
Jason, US, Memphis, 1000
David, US, "Little Rock", 8765453
"Karam, Sage", US, Nazareth, 4678
"David, simon", US, Chicago, 1234

Answer 5

Using Raku (formerly known as Perl_6)

~$ raku -MText::CSV -e 'my @a = csv(in => $*IN, sep => ", ");  
                        for ^@a[0].elems -> $i {
                            @a>>.[$i] = @a>>.[$i].map: *.subst(:global, / \d <(\,)> \d / ) 
                        };  csv(in => @a, out => $*OUT, sep => ", ");'  < file

#OR:


~$ raku -MText::CSV -e 'my @a = csv(in => "/path/to/file", sep => ", "); 
                        for ^@a[0].elems -> $i { 
                            @a>>.[$i] = @a>>.[$i].map: *.subst(:global, / \d <(\,)> \d / ) 
                        };  csv(in => @a, out => $*OUT, sep => ", ");'

Above are answers written in Raku, a member of the Perl-family of programming languages. Here Raku's Text::CSV module (an authentic CSV parser) is used to massage the data into RFC 4180 specification, the only exception being that the OP desires ", " as separator in the output. Above, data is read using Text::CSV's high-level csv() method, which can take input as $*IN stdin, or as a filepath.

Briefly, the data is stored in an array, taking care to decode the input file with the correct ", " separator. In the second statement (using the Header Row to get the number of columns), each Row is mapped into sequentially, and substituted with the desired change: :globally change each field such that / \d <(\,)> \d / if a comma is found immediately flanked on both sides by a digit, replace that comma with nothing (i.e. delete it). Raku's <( … )> capture markers are used to only delete the comma character: while the full regex gets matched, the capture markers tell Raku to drop anything outside (and only substitute/delete what's inside).

Sample Input (note corrected Header Row):

Name, Country, City, Amount
Jason, US, Memphis, "1,000"
David, US, Little Rock, "8,765,453"
"Karam, Sage", US, Nazareth, "4,678"
"David, simon", US, Chicago, "1,234"

Sample Output:

Name, Country, City, Amount
Jason, US, Memphis, 1000
David, US, "Little Rock", 8765453
"Karam, Sage", US, Nazareth, 4678
"David, simon", US, Chicago, 1234

Note: There isn't any error-checking on the number of fields per row, so if your CSV has an incorrect header or short/long row, parsing will be problematic. Note also that without commas in the fourth column, RFC4180 says that column doesn't have to be quoted (conversely, the city "Little Rock" becomes a quoted field in the output because it contains whitespace).

https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://docs.raku.org
https://raku.org