Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-05
12
Medium Priority
?
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 20

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 12

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 12

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
 
LVL 12

Accepted Solution

by:
tel2 earned 2000 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 12

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 30

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 12

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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.
Suggested Courses

670 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