Solved

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

Posted on 2016-10-05
12
68 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 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 28

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
Utilizing an array to gracefully append to a list of EmailAddresses
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…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

777 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