How to Remove Commas Inside of Quotes From a CSV or Text File on Linux with Awk
Submitted by ingram on Wed, 07/22/2015 - 2:22pm
Let's say you have a CSV file that you would like to open in Excel or LibreOffice Calc to analzye. You find out the formatting is broken because there is a column that has quoted text with a comma in it:
Name, Country, City
Jason, US, Memphis
David, US, Little Rock
"Karam, Sage", US, Nazareth
As you can see, this may cause a problem when importing the file into your spreadsheet application as the 4th row has 4 commas, and the others only have 3.
This is easy to fix using Awk. Simply run this one-liner command, specifying your source file, and then a new file to output it to:
awk -F'"' -v OFS='' '{ for (i=2; i<=NF; i+=2) gsub(",", "", $i) } 1' source_file.csv >processed_file.csv
Bookmark/Search this post with
- Add new comment
- 1 comment
My Input is:
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 out put 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.
Post new comment