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
# 8  
Old 09-29-2011
am using ksh.

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

here is the error

cmrwc7:/wcdev/home/db_maint> perl -MText::ParseWords -nle'   @f = parse_line

>    @f = parse_line(",",2, $_);   tr/,//d for @f;

   print join ",", @f

  ' tmp.csv

>    tr/,//d for @f;  ' tmp.csv

>    print join ",", @f  ' tmp.csv

>   ' tmp.csv

syntax error at -e line 3, near "tr/,//d for "

Execution of -e aborted due to compilation errors.

Last edited by radoulov; 09-29-2011 at 08:26 AM.. Reason: Code tags.
# 9  
Old 09-29-2011
Try the following:

1. Create a new file with the following content:

#!/usr/bin/perl -w

use strict;
use Text::ParseWords;

while (<>) {
  my @f = parse_line(',', 2, $_);
  tr/,//d for @f;
  print join ',', @f;

Assign the proper permissions:

chmod u+x your_new_file

Execute the script like this:

./your_new_file tmp.csv

# 10  
Old 09-29-2011
Another one using awk
awk -F"," '{for(i=1;i<=NF;i++){t=gsub(/"/,"\"",$i);if(t==1){$i=$i $(++i)}printf $i OFS;if(i==NF)print "\n"}}' 
OFS="," input_file | sed 's/,$//g'


---------- Post updated at 05:28 AM ---------- Previous update was at 05:26 AM ----------

well, this will take care of only 1 comma if it occurs within "...".

# 11  
Old 09-29-2011
Another approach could be:
awk -F\" '{for(i=2;i<=NF;i+=2)gsub(",",x,$i)}1' OFS=\" file > newfile

Last edited by Franklin52; 09-30-2011 at 03:11 AM.. Reason: Typo, thanks Alister
This User Gave Thanks to Franklin52 For This Post:
# 12  
Old 09-29-2011
sed 's/",/&_X_/g;s/\("[^ ]*\),\( [^ ]*"\),_X_/\1\2,/g;s/_X_//g' infile

# 13  
Old 09-29-2011
Yet another way with [n]awk...
awk -F, '{
    for (i=1; i<=NF; i++) {
        if (s) {
           if ($i ~ /^[^"].*\"$/) {printf("%s,", s $i); s=0}
           else s = s $i
        else if ($i ~ /^["].*[^"]$/) s = $i
        else printf("%s%s", $i, i==NF?"\n":FS)
}' file

# 14  
Old 09-29-2011
The following approach handles any number of embedded commas (even when the comma-delimited, double-quoted field contains both commas and escaped quotes). It assumes that the input does not contain any bytes with value of 0x01 (usually a safe assumption).

tr '\n,' '\001\n' < d | 
awk -F\" '{while ((NF-1)%2) {o=$0; getline; $0=o$0}; printf("%s%s", r, $0); r=ORS}' |
tr '\n\001' ',\n'

Basic test:
$ cat d
" ,,"",,"", A,,,,,A ,"" A,,,,,",0,0
$ tr '\n,' '\001\n' < d |
> awk -F\" '{while ((NF-1)%2) {o=$0; getline; $0=o$0}; printf("%s%s", f, $0); f=ORS}' | tr '\n\001' ',\n' 
" """" AA "" A",0,0


---------- Post updated at 01:20 PM ---------- Previous update was at 12:42 PM ----------

Originally Posted by Franklin52
Another approach could be:
awk -F\" '{for(i=2;i<=NF;c+=2)gsub(",",x,$i)}1' OFS=\" file > newfile

Nice. Your approach can handle the same situations as mine but is much more concise and efficient. Looking back, I'm not certain how I managed to arrive at such a needlessly complex solution.

It's an obvious, minor typo, but in case anyone trying to use this code experiences an infinite loop, note that the variable c should be i.

This User Gave Thanks to alister For This Post:
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