Removing comma "," in a field value in csv file

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Removing comma "," in a field value in csv file
# 1  
Old 09-29-2011
Debian Removing comma "," in a field value in csv file


I have csv file with records as below. Now i have remove any comma in the filed value because that creates problem when i feed this file to an application.

for example below are two sample records, the second record have a comma in ", Inc." field, now i have to remove this comma. Please advise a method to do this.

"20110922","WSJ WE","A001","1NA",148,0,0,148,0,"United States","PG01","ORACLE","Exadata","GENL","B160","ZENITH",291001168,"SECA","GENL"

"20110919","WSJ EE","A001","1NA",148,0,0,148,0,"United States","PG01",", Inc.","Salesforce chatter","GENL","B241",", Inc.",291008167,"SECA","GENL"


Moderator's Comments:
Mod Comment Video tutorial on how to use code tags in The UNIX and Linux Forums.

Last edited by radoulov; 09-29-2011 at 07:25 AM..
# 2  
Old 09-29-2011
nawk '{sub("com,","com",$0);print}' input.csv

# 3  
Old 09-29-2011
$ sed 's,.com\, ,.com ,g' infile
"20110919","WSJ EE","A001","1NA",148,0,0,148,0,"United States","PG01"," Inc.","Salesforce chatter","GENL","B241"," Inc.",291008167,"SECA","GENL"

# 4  
Old 09-29-2011
how to make it generic? the comma may not precede ".com" always. It can be in any field.
# 5  
Old 09-29-2011
With GNU awk 4 (removes the comma from any field):

awk '{ 
  for (i = 0; ++i <= NF; )
   sub(/,/, x, $i)  
  }1' OFS=, FPAT='([^,]+)|("[^"]+")' infile

With Perl:

perl -MText::ParseWords -nle'
   @f = parse_line(",",2, $_);
   tr/,//d for @f;
   print join ",", @f
  ' infile

# 6  
Old 09-29-2011

awk dint work and perl gave the below error

syntax error at -e line 1, near "tr/,//d for "
Execution of -e aborted due to compilation errors.
# 7  
Old 09-29-2011
Originally Posted by anaga

awk dint work
Do you have GNU awk 4?

... and perl gave the below error

syntax error at -e line 1, near "tr/,//d for "
Execution of -e aborted due to compilation errors.
Which shell are you using? Try running the command with a shell different than the (t)csh.
Could you please post the exact command and the complete output that you get?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Bash script - Print an ascii file using specific font "Latin Modern Mono 12" "regular" "9"

Hello. System : opensuse leap 42.3 I have a bash script that build a text file. I would like the last command doing : print_cmd -o page-left=43 -o page-right=22 -o page-top=28 -o page-bottom=43 -o font=LatinModernMono12:regular:9 some_file.txt where : print_cmd ::= some printing... (1 Reply)
Discussion started by: jcdole
1 Replies

2. Shell Programming and Scripting

Removing duplicates on a single "column" (delimited file)

Hello ! I'm quite new to linux but haven't found a script to do this task, unfortunately my knowledge is quite limited on shellscripts... Could you guys help me removing the duplicate lines of a file, based only on a single "column"? For example: M202034357;01/2008;J30RJ021;Ciclo 01... (4 Replies)
Discussion started by: Rufinofr
4 Replies

3. UNIX for Dummies Questions & Answers

Using "mailx" command to read "to" and "cc" email addreses from input file

How to use "mailx" command to do e-mail reading the input file containing email address, where column 1 has name and column 2 containing “To” e-mail address and column 3 contains “cc” e-mail address to include with same email. Sample input file, email.txt Below is an sample code where... (2 Replies)
Discussion started by: asjaiswal
2 Replies

4. Shell Programming and Scripting

Awk,sed : change every 2nd field ":" to "|"

Hi Experts, I have a string with colon delimited, want 2nd colon to be changed to a pipe. data: 101:8:43:4:72:14:41:69:85:3:137:4:3:0:4:0:9:3:0:3:12:3: I am trying with sed, but can change only 1 occurance: echo "101:8:43:4:72:14:41:69:85:3:137:4:3:0:4:0:9:3:0:3:12:3:" | sed 's/:/|/2'... (5 Replies)
Discussion started by: rveri
5 Replies

5. Shell Programming and Scripting

Removing "^M" from the end of a String (i.e. "Ctrl+M")?

Hello All, I have an Expect script that ssh's to a remote server and runs some commands before exiting. One of the commands I run is the "hostname" Command. After I run this command I save the output using this line in the code below... Basically it executes the hostname command, then I... (2 Replies)
Discussion started by: mrm5102
2 Replies

6. Shell Programming and Scripting

Substituting comma "," for dot "." in a specific column when comma"," is a delimiter

Hi, I'm dealing with an issue and losing a lot of hours figuring out how i would solve this. I have an input file which looks like this: ('BLABLA +200-GRS','Serviço ','TarifaçãoServiço','',2985,0,55,' de conversão em escada','Dia','Domingos') ('BLABLA +200-GRR','Serviço... (6 Replies)
Discussion started by: poliver
6 Replies

7. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

8. UNIX for Dummies Questions & Answers

Explanation of "total" field in "ls -l" command output

When I do a listing in one particular directory (ls -al) I get: total 43456 drwxrwxrwx 2 root root 4096 drwxrwxrwx 3 root root 4096 -rwxrwxr-x 1 nobody nobody 3701594 -rwxrwxr-x 1 nobody nobody 3108510 -rwxrwxr-x 1 nobody nobody 3070580 -rwxrwxr-x 1 nobody nobody 3099733 -rwxrwxr-x 1... (1 Reply)
Discussion started by: proactiveaditya
1 Replies

9. Shell Programming and Scripting

Removing special characeter "~V" in a unix file

I have the Unix XML file as below: <?xml version="1.0" encoding="UTF-8"?> <ReportData version="1.0"><DisplayName>Non-Agency CMO Daily Trade Recap - Hybrids</DisplayName><ReportType>MgmtTradingReport</ReportType><Description>Management Trading... (7 Replies)
Discussion started by: mohsin.quazi
7 Replies

10. Solaris

removing "/" file system from solaris volume

Hi all, I have created a volume for the root device as d0 and the sub mirror for same is d10. the output from metastat d0 is as below I want to clear these volume , as i cant unmount the "/ " file system , please suggest as how can i clear this. Also the required entries are there... (2 Replies)
Discussion started by: kumarmani
2 Replies
Login or Register to Ask a Question