Find and replace entries in one csv file using another with bash

Question

Main file:

A B
C D
D A
G H

Ref file:

1 A
2 B
3 C
4 D
5 G
6 H

New file:

I wanna do the above replacement, how can I do that using awk or some simple command line?

Hi! you should try to find a solution first, and then provide an mvce, so we can provide better answers on your issue. — Ulysse BN
– Ulysse BN, Commented Jun 29, 2017 at 20:12
Apologies, I did try to look for a solution. I ended up finding one that does replacement in a different way and it didn't have an explanation for the command line. Wanted a better answer than that. — Blizzard
– Blizzard, Commented Jun 29, 2017 at 20:56

RomanPerekhrest · Accepted Answer · 2017-06-29 20:32:17Z

1

awk solution:

awk 'NR==FNR{ a[$2]=$1; next }{ $1=a[$1]; $2=a[$2] }1' reffile mainfile

The output:

a[$2]=$1 - capturing numbers from reffile into array indexed by letters (e.g. a["A"]=1)
$1=a[$1]; $2=a[$2] - replacing letters in mainfile with respective numbers

answered Jun 29, 2017 at 20:32

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

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

4 Comments

Blizzard Over a year ago

Thank you! This answers it.

Blizzard Over a year ago

a quick follow up. If the two columns had the same entry, it is only replacing one and removing the other. Is that normal? For example if a row was A A the output comes out to be just 1.

RomanPerekhrest Over a year ago

@Blizzard, I've just tested on A A record and it gave me 1 1. Should work

Blizzard Over a year ago

Oh! I had missing values in the ref file. It works splendidly, thank you!

Collectives™ on Stack Overflow

Find and replace entries in one csv file using another with bash

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related