Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
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
Hi,

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 "Salesforce.com, Inc." field, now i have to remove this comma. Please advise a method to do this.

Code:
"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","Salesforce.com, Inc.","Salesforce chatter","GENL","B241","Salesforce.com, Inc.",291008167,"SECA","GENL"

Thanks,
Naga

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
Code:
 
nawk '{sub("com,","com",$0);print}' input.csv

# 3  
Old 09-29-2011
Code:
$ sed 's,.com\, ,.com ,g' infile
"20110919","WSJ EE","A001","1NA",148,0,0,148,0,"United States","PG01","Salesforce.com Inc.","Salesforce chatter","GENL","B241","Salesforce.com 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):

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

With Perl:

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

# 6  
Old 09-29-2011
@radoulov

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
Quote:
Originally Posted by anaga
@radoulov

awk dint work
Do you have GNU awk 4?

Quote:
... 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','wap.bla.us.0000000121',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