• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

replace content of certain columns in a *.csv file by a defined string

Dear experts,
I have to replace the content of certain columns in a *.csv file by a defined string using a unix shell script on AIX.
e.g. CSV file before processing

11111,2222,3333,4444,5555
aaaaa,bbb,ccc,dd,ee

now after replacing column 2 and 4 by ***:
11111,***,3333,***,5555
aaaaa,***,ccc,***,ee

Many thanks
0
mruff
Asked:
mruff
1 Solution
 
simon3270Commented:
You could use awk for this:
awk -F, 'BEGIN{OFS=","}{$2="***";$4="***";print}' input_file.csv

Open in new window

1
 
tel2Commented:
Hi mruff,

Here's a Perl alternative to Simon's answer (i.e. output goes to STDOUT):
    perl -F, -ane '$F[1]=$F[3]="***";print join(",",@F)' input_file.csv
Or, overwrite the input file with the output:
    perl -i -F, -ane '$F[1]=$F[3]="***";print join(",",@F)' input_file.csv
Or, backup the input file to input_file.csv.bak then overwrite the original input file with the output:
    perl -i.bak -F, -ane '$F[1]=$F[3]="***";print join(",",@F)' input_file.csv
0
 
mruffAuthor Commented:
Dear experts,
Many thanks, I've tested the awk -work well
One point I forgot, who to replace if in the csv file the Separator is the Group separator

Seq      Dec      Hex      Acronym      Symbol      Name        
^]      29      1D      GS              ␝              Group Separator

Many thanks
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
mruffAuthor Commented:
Dear experts,
Sorry previous comment had typos
Many thanks, I've tested the awk -work well
One point I forgot, how to replace if in the csv file the Separator is the Group separator

Seq      Dec      Hex      Acronym      Symbol      Name        
^]      29      1D      GS              ␝              Group Separator

Many thanks
0
 
tel2Commented:
Hi mruff,

If you have typos in a comment, you can correct them by clicking the down-arrow in the top-right corner, then clicking "Edit this comment".  Similarly, you can delete comments using the "Delete this comment" link.
Both options are available until the next post is made (by you or someone else).

Did you try my Perl solutions?  Do you want to overwrite the original file with the new data, or what?

I don't understand your last post.  For starters, what do you mean by Group Separator?  And you have "GS" in 2 different font sizes - why?
0
 
mruffAuthor Commented:
Hi tel2,
Yes, thank you, just tested the per script, it works wonderful
My question now is, how to pass a non printable character, which is the actual column delimiter as argument.
In the file I have to process the column delimiter is the ASCII control character 'Group Separator':
http://www.theasciicode.com.ar/ascii-control-characters/group-separator-ascii-code-29.html
0
 
tel2Commented:
OK, I'm now I'm with ya.

ASCII 29 decimal = 35 octal.
perl -F"\35" -ane '$F[1]=$F[3]="***";print join("\35",@F)' input_file.csv
1
 
mruffAuthor Commented:
Hi tel2,
Perfect, works great, thx!
0
 
tel2Commented:
Thanks for the points, mruff.
Personally, I think Simon deserved a share of them, because he did give a good solution to your original question, but it's up to you.  But I do need the points more than he does.  8)

BTW, I don't know much awk, but I see that it can handle your GS request similarly to how Perl does:
    awk -F"\35" 'BEGIN{OFS="\035"}{$2=$4="***";print}' input_file.csv
Perl is more of a full-blown language though, so can be used for almost anything.
0
 
serialbandCommented:
Perl just reused a lot of standard unix tool syntax.  It was intended to allow them to easily adopt and transition to Perl.  Being a single monolithic tool makes it easier to keep Perl in sync across all platforms, making Perl code much more portable than shell scripts.
0
 
mruffAuthor Commented:
@tel2 Good point how can I divide the points after having accepted the Solution?
0
 
tel2Commented:
Click the "Request Attention" link at the bottom-right of your original post, then ask for the question to be reopened so you can reassign points.  After a moderator does that, you can assign the points as you wish.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now