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
Like this article?? Check out these:
User login
Popular content
Today's:
All time:
Last viewed:
Did you find this tutorial useful?
To assist with the bills and the author's time, please consider making a donation. Any amount helps:
Top Categories
Linux
Command Line
ubuntu
windows
Terminal
Windows 7
Exchange 2010
Exchange
Open Source
hosted exchange
hacking
Bible
Christain
SSH
Multi-Tenant
HP
WINE
Server 2008 R2
Switch
Password Recovery
Active Directory
Internet Explorer
nmap
One-Liner
Lync
Citrix
Games
Citrix Receiver
DNS
OpenPanel
Server
ping
Pure-FTPd
Script
RAID
robocopy
server 2008
SonicWall
profile migration
Windows 8
VPN
VM
tweak
Unity
PsExec
ESX
Outlook
BSOD
Awk
ASCII
Control Panel
iis
Exchange 2010 SP1
iptables
IE9
Fail2ban
IE ESC
7
2012
2008
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