Replacing field from file 1 with field from file 2

Shell Programming and Scripting
1

I've been searching these forums for the past 2 hours and i've found stuff that almost works but not quite.

i have file 1:

A, 1, Bla, 123
B, 2, Bla2, 123
C, 3, Bla3, 123
D, 4, Bla4, 123

file 2:

1, Aha
3, Ok

Output:

A, 1, Aha, 123
B, 2, Bla2, 123
C, 3, Ok, 123
D, 4, Bla4, 123

I thought I could do a join on file 1 and file 2, however join will insert file 2 into the first two rows, and incorrect merge the file with the -a1 flag.
I figured I could join and simply pipe it to awk to do a stupid print of only the specific columns I need, but join didn't work right for me.

I tried awk, but I've only managed to append the values to all of the rows.

Anyhelp would be appreciated,

thanks in advanced.

2

YMMV:

nawk -F, 'FNR==NR{f2[$1]=$2;next} $2+0 in f2 {$3=f2[$2+0]}1' OFS=, file2 file1
1 Like
3

Rather than having someone start from scratch, please share what you have done so far. Especially if you are that close to an answer.

4

sure,

i'm working through vgersh99's solution right now so will need to try and get it working with the actual example (so I can modify it going forward).

join -t"," -1 2 -2 1 file1.txt file2.txt

that gives me

A, 1, Bla, 123, 1, Aha
B, 2, Bla2, 123, 2, Ok
C, 3, Bla3, 123
D, 4, Bla4, 123

which is not expected.

vgersh99: thanks for the reply

can I ask what

FNR==NR{f2[$1]=$2;next} --copies the second value from file 2 into an array
$2+0 in f2 -- What does this line do?
{$3=f2[$2+0]}1' --this affects file1, if the $3 file matches your value in f2, print buffer
OFS=, file2 file1

is that right?

5 
FNR==NR{f2[$1]=$2;next} --copies the second value from file 2 into an array
# FNR==NR is true for the FIRST file to be processed 
# f2 - an array index by the value of the FIRST field with the value of the SECOND field (from file2)

$2+0 in f2 -- What does this line do?
# We get here we already processed file2 - here we're dealing with file1
# $2+0 - the value of the SECOND field. As it seems that you have a leading spaces in file 1,
#        by adding '0' we convert a field value to an int
# $2+0 in f2 - the value of the second field of file1 is in array 'f2'
#              '$2+0' is one of the indexes (not values) of array f2 

#
{$3=f2[$2+0]}1' --this affects file1, if the $3 file matches your value in f2, print buffer
# assign the the value from array f2 indexed by '$2+0' to the value of the THIRD field.
OFS=, file2 file1
6

mm what if i wanted the second field to match, but replace the 3rd field?

7

This is what the code does now!
Unless I'm misunderstanding your question.
Post sample data and the desired output.

8

Quite embarassingly, I'm trying to convert what you wrote into the true file i'm working with which has over 130 fields.

But trying with your sample it returns 4 blank lines.

I think I got it though,

thanks very much for the informative answer.

---------- Post updated at 04:46 PM ---------- Previous update was at 04:35 PM ----------

Got it, stupidity of my part.

Just needed a cup of joe to see the light.