Solved

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

Posted on 2016-10-05
12
90 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 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 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
Technology Partners: 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 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 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 29

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

I have been reconstructing a PHP-based application that has grown into a full blown interface system over the last ten years by a developer that has now gone into business for himself building websites. I am not incredibly fond of writing PHP code o…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

751 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