Replace string in multiple columns in a csv

Question

Consider the sample below:

BRANCH|TXN_ID|CUSTOMER|PROCESS_DATE|VALUE_DATE|AMOUNT|UPLOAD_DATE|NARRATIVE
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST
1|23234|12343|20141030|20141030|2000|20141030|TEST

If I use sed 's/20141030/20141029/g', it will replace all instances of 20141030, including UPLOAD_DATE, which is not what I want.

From the net, the awk examples am getting only replace one instance of the string. I need to replace all instances in one go

So my question is, how can I replace the contents of column 4 and 5 (process date and value date) using a unix script while maintaining the format of the file? The result will be written into a new file.

Tom Fenech · Accepted Answer · 2014-10-30 11:03:25Z

4

Using awk

awk 'BEGIN{FS=OFS="|"}{sub(20141030,"20141029",$4); sub(20141030,"20141029",$5); print}' inputFile

Gives output as

BRANCH|TXN_ID|CUSTOMER|PROCESS_DATE|VALUE_DATE|AMOUNT|UPLOAD_DATE|NARRATIVE
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST
1|23234|12343|20141029|20141029|2000|20141030|TEST

edited Oct 30, 2014 at 11:03

Tom Fenech

75.1k13 gold badges119 silver badges154 bronze badges

answered Oct 30, 2014 at 10:19

nu11p01n73R

26.8k3 gold badges42 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jotne Over a year ago

@TomFenech. I was just to post a comment about FS/OFS my self. You should not change a code for a user, but post a comment, and the user could then change it if i like to do it.

Tom Fenech Over a year ago

@Jotne for a major edit I agree, although in this case the changes are largely cosmetic. You or the OP are welcome to roll back the edit if you disagree with it.

fedorqui Over a year ago

@Jotne it is completely fine to edit someone's answer if it improves it like now. See stackoverflow.com/help/editing

Jotne Over a year ago

@TomFenech I do agree with you in some case, but posting a comment is better, since then the poster may learn some and he/she may remove some bad habits.

Tom Fenech Over a year ago

No problem. @Jotne I appreciate that it's a grey area. I tried to leave a helpful edit message for that reason :)

|

score 1 · Accepted Answer · 2014-10-30 11:53:01Z

1

nu11p01n73Rs answer is definitely the best solution for this specific problem but I though i would add a more general approach that could work for lots of fields.

Just add all the fields you want to change to the start of the split statement.

awk -F"|" -vOFS="|" '{split("4 5",A," ");for(i in A)sub(20141030,"20141029",$A[i])}1' file

Actually this would be more efficient.

  awk 'BEGIN{FS=OFS="|";split("4 5",A," ")}{for(i in A)sub(20141030,"20141029",$A[i])}1' file

edited Oct 30, 2014 at 11:53

answered Oct 30, 2014 at 11:11

user3442743

Comments

NeronLeVelu · Accepted Answer · 2014-10-30 10:44:29Z

0

sed 's/^\([0-9]*|\)\{3\}\)20141030/\120141029/' YourFile

Using sed (posix version so us --posix on GNU sed especially with the | inside)

answered Oct 30, 2014 at 10:44

NeronLeVelu

10.1k1 gold badge26 silver badges44 bronze badges

Collectives™ on Stack Overflow

Replace string in multiple columns in a csv

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related