I have a fileA.txt which looks like this:

title              trial_exp  values
Version            1.0
Blank1                        0.010
Blank2                        0.200
Blank3                        0.100
Test_field_Asia               1.500
Test_field_Europe             0.900
Test_field_America            2.000

I have then a fileB.txt which looks like:

Test_field_Asia     Thailand
Test_field_Europe   UK
Test_field_America  Mexico

I want to match all the names in column 1 in fileA with the names in column 1 of fileB and replace the values in column 2 when a match is found:

Desired output:

title               trial_exp   values
Version             1.0
Blank1                          0.010
Blank2                          0.200
Blank3                          0.100
Test_field_Asia     Thailand    1.500
Test_field_Europe   UK          0.900
Test_field_America  Mexico      2.000

I tried the following code:

awk 'NR==FNR{rec[$1]=$2;next}{temp=$1} temp in rec{$2=rec[temp]}1' fileA.txt fileB.txt

However, the replacement is not done and I get the original fileA.txt printed ou

Can anybody explain to me what is wrong in this code or suggest alternative solution, please?

  • (1) Are you sure you didn't run $ awk ...? (2) What is your field separator? It doesn't look like it's tab (e.g. Blank1<tab><tab>0.010) or a space character. How do you determine that the second field is empty and the values belong to the third field in fileA?
    – Freddy
    Jan 23 at 6:53
  • Please edit your sample input and expected output accordingly so that the question contains data we can work with.
    – Freddy
    Jan 23 at 11:04
  • awk 'NR==FNR{A[$1]=$2;next}$3 in A{$3=A[$3]}1' file2 file1 Jan 23 at 14:07
  • apparently that worked Jan 23 at 14:08

This is the solution.

  1. Create the file myscript.sh
awk 'BEGIN{FS=",";OFS="\t"}
    FNR==NR{myfile2[$1]=$2; next}
    FNR>1{if(myfile2[$1]){print $1, myfile2[$1], $3,NR}else{print $1,$2,$3}}
' fileB.txt fileA.txt
  1. Execute it:
chmod +x myscript.sh

You'll get this output:

Version 1.0 
Blank1      0.010
Blank2      0.200
Blank3      0.100
Test_field_Asia Thailand    1.500   9
Test_field_Europe   UK  0.900   10
Test_field_America  Mexico  2.000   11
  1. If you want the comma as separator in your output you can change: OFS="," you'll get this output:
  1. Optional. If you want to include the fileA.txt header (first line) a) you can simply change the conditional: FNR>=1 or b) rewrite the script in a short way:
awk 'BEGIN{FS=",";OFS="\t"}
    FNR==NR{myfile2[$1]=$2; next}
    myfile2[$1]{print $1, myfile2[$1], $3; next;}{print $1,$2,$3}
' fileB.txt fileA.txt

Finally if you want a detailed example, read: https://www.baeldung.com/linux/awk-multiple-input-files

  • hi, I tried your code but I get the original fileA without sub in the 2nd column Jan 24 at 4:14
  • hi sorry, I think it worked, just add to add the same field separator "," in the fileB same as fileA because it did nit have it Jan 24 at 4:22
  • please, can you explain to me the second part of the code after next? Jan 24 at 4:26
  • 1
    All lines of first file (fileB.txt) are read because the next instruction. FNR>1 is used to omit the header of the second file (fileA.txt). If you want to print the header line too, just omit it this condition. About FNR and NR: FNR, Number of Records relative to the current input file. NR(Number of Records Variable), NR gives you the total number of records being processed (in your example the total records of the two files). So, when this script are reading the fileB.txt FNR and NR are equal. When script are reading the second file, FNR restart to 1. Jan 24 at 9:32
  • 1

What you want is essentially a database join. There is a command for that, aptly named join. The problem is that it requires sorted input. If the line order is not relevant, you can do this:

join -a1 <(sort fileA.txt) <(sort fileB.txt)

The -a1 option prints lines that can't be joined. The two <(...) constructs generate temporary files that contain the output of the sort commands. Using your example, the result is

Blank1 0.010
Blank2 0.200
Blank3 0.100
Test_field_America 2.000 Mexico
Test_field_Asia 1.500 Thailand
Test_field_Europe 0.900 UK
title trial_exp values
Version 1.0

(I just realize that by copying your example, I replaced tabs with spaces)

The title and Version lines are probably meant to be headers, so that some post-processing would be required.

  • hi,thanks, I was also thinking about that, but for me maintaining the order of the lines is necessary Jan 24 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.