Solved

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

Posted on 2016-10-05
12
59 Views
Last Modified: 2016-10-07
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
Comment
Question by:mruff
12 Comments
 
LVL 19

Expert Comment

by:simon3270
ID: 41830847
You could use awk for this:
awk -F, 'BEGIN{OFS=","}{$2="***";$4="***";print}' input_file.csv

Open in new window

1
 
LVL 11

Expert Comment

by:tel2
ID: 41830945
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
 

Author Comment

by:mruff
ID: 41832207
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
 

Author Comment

by:mruff
ID: 41832209
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
 
LVL 11

Expert Comment

by:tel2
ID: 41832664
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
 

Author Comment

by:mruff
ID: 41833059
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 11

Accepted Solution

by:
tel2 earned 500 total points
ID: 41833069
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
 

Author Comment

by:mruff
ID: 41833098
Hi tel2,
Perfect, works great, thx!
0
 
LVL 11

Expert Comment

by:tel2
ID: 41834410
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
 
LVL 27

Expert Comment

by:serialband
ID: 41834664
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
 

Author Comment

by:mruff
ID: 41834698
@tel2 Good point how can I divide the points after having accepted the Solution?
0
 
LVL 11

Expert Comment

by:tel2
ID: 41834710
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Java performance on Solaris - Managing CPUs There are various resource controls in operating system which directly/indirectly influence the performance of application. one of the most important resource controls is "CPU".   In a multithreaded…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now