Merge two files AWK

Question

I have to merge two files and need help with:

File1.csv

mac-test-2,10.57.8.2,Compliant
mac-test-6,10.57.8.6,Compliant
mac-test-12,10.57.8.12,Compliant
mac-test-17,10.57.8.17,Noncompliant

File2.csv

mac-test-17,10.57.8.17,2022-10-21

After Merge the content should be Merge.csv

mac-test-2,10.57.8.2,Compliant,NA
mac-test-6,10.57.8.6,Compliant,NA
mac-test-12,10.57.8.12,Compliant,NA
mac-test-17,10.57.8.17,Noncompliant,2022-10-21

so logic is if the File1.txt doesnt have a matching record in File2.txt then "NA" should be inserted and if it is a match then date should be inserted in the fourth column.

I have written below

awk -F "," '
ARGV[1] == FILENAME{a[$1];next}
{
 if ($1 in a) {  
    print $0 ","
 } else {
  print $0 ",NA"
 }
}
' File2.csv File1.csv

But this is printing

mac-test-2,10.57.8.2,Compliant,NA
mac-test-6,10.57.8.6,Compliant,NA
mac-test-12,10.57.8.12,Compliant,NA
mac-test-17,10.57.8.17,Noncompliant,

I am not sure how I can print the date if it matches.

Your attempt (and the accepted question) assume, that File2.csv contains at most one line. Can your really assume this? — user1934428
– user1934428, Commented Oct 10, 2022 at 6:09
@user1934428, there is already a solution which takes care in case it's an empty file was added by me in answer. — RavinderSingh13
– RavinderSingh13, Commented Oct 10, 2022 at 6:10
@RavinderSingh13: Empty file, yes. That's why I wrote at most one line. But how about a file with i.e. 2 lines? — user1934428
– user1934428, Commented Oct 10, 2022 at 6:14
@user1934428, an array is being created to make comparison from file1.csv so IMHO it doesn't matter how many lines are there. — RavinderSingh13
– RavinderSingh13, Commented Oct 10, 2022 at 6:17

RavinderSingh13 · Accepted Answer · 2022-10-10 06:11:11Z

2

With your shown samples please try following awk code. Written and tested with your shown samples only.

awk '
BEGIN{ FS=OFS="," }
FNR==NR{
  arr[$1]=$NF
  next
}
{
  print $0,($1 in arr?arr[$1]:"NA")
}
' file2.csv file1.csv

To handle empty file2.csv please try following awk program.

awk '
BEGIN{ FS=OFS="," }
ARGV[1] == FILENAME{
  arr[$1]=$NF
  next
}
{
  if ($1 in arr) {
    print $0,arr[$1]
  }
  else{
    print $0,"N/A"
 }
}' file2.csv file1.csv

edited Oct 10, 2022 at 6:11

answered Oct 10, 2022 at 3:53

RavinderSingh13

135k14 gold badges61 silver badges100 bronze badges

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

5 Comments

snigdha singh Over a year ago

This works when file2 has data and if file2 is empty it doesnt attach NA to all the lines in File1. Please help

RavinderSingh13 Over a year ago

@snigdhasingh, looks like it was not mentioned in your question . Let me add that in a min here.

snigdha singh Over a year ago

sorry, I figured out. Thanks for your help! :)

RavinderSingh13 Over a year ago

@snigdhasingh, Good to hear that and happy to help you. I have added alternative solution also which handles the empty file2.csv scenario also. Cheers and happy learning.

anubhava Over a year ago

Good to see use of ARGV[1] in first condition to handle empty files

Collectives™ on Stack Overflow

Merge two files AWK

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related