Vlookup using awk command

0
votes

I have two files in my linux server.

File 1

9190784
9197256
9170546
9184139
9196854

File 2

S NO.,Column1,Column2,Column3
72070,9196854,TGM,AP
72071,9172071,BGM,MP
72072,9184139,AGM,KN
72073,9172073,TGM,AP

I want to write a script or a single line command in bash using awk command, so as whatever the element in File -1 should match the same with column 1 in File -2 and print Column 1, Column2 and Column3. Also if any entry is not found it should print entry from file 1 and print NA in Column 2 and Column 3

Output : it should redirect the output to a new file as below. new_file

9190784,TGM,AP
9197256,NA,NA
9170546,NA,NA
9184139,AGM,KN
9196854,TGM,AP

I hope the query is understandable. Anyone please help me on the same.

2
bashawk
As you see 2 VOTES are there for closing this question so it is not clear, please add samples of input and output in your post too and let us know then? – RavinderSingh13
What does the command cat * | cut -d "|" -f 2 | awk '$1 {print substr($1,1,7)}'| head -5 have to do with file 1 and the list of strings you show under it? Is that a command you're running on file 1 to produce those strings as output (in which case just show us file 1) or is it the command thats generating file 1 or something else? The columns you say you want to print don't match the output you say you want. Tidy up your question to be clear and consistent so we can help you. – Ed Morton
Hi Ed, The cat * Command has nothing to do with file, it was just a way i was extracting the file nothing else. to make it more clear i have edited the question. Can you help now, – Aakashdeep Garg

2 Answers

0
votes

Does it have to be awk? It's done with join:

Having two files:

echo '9190784
9197256
9170546
9184139
9196854' >file2

echo 'S NO.,Column1,Column2,Column3
72070,9196854,TGM,AP
72071,9172071,BGM,MP
72072,9184139,AGM,KN
72073,9172073,TGM,AP' > file1

One can join the on , as separator on the second field from the first file1 -12 with removed the first header line tail -n +2 and sorted using the second field sort -t, -k2 with the first field from the second file -21 sorted sort.

join -t, -12 -21 -o1.2,1.3,1.4 <(tail -n +2 file1 | sort -t, -k2) <(sort file2)

will output:

9184139,AGM,KN
9196854,TGM,AP
0
votes

standard join operation with awk

$ awk 'BEGIN   {FS=OFS=","} 
       NR==FNR {a[$2]=$3 OFS $4; next} 
               {print $1, (($1 in a)?a[$1]:"NA" OFS "NA")} file2 file1

substring variation (not tested)

$ awk 'BEGIN   {FS=OFS=","} 
       NR==FNR {a[substr($2,1,7)]=$3 OFS $4; next} 
               {key=substr($1,1,7);
                print $1, ((key in a)?a[key]:"NA" OFS "NA")} file2 file1