Merge two files and combine data
 

Dear friends, I need to merge two files, each file has 5 columns, I need to merge files based on first four columns. All fields do not have data and an empty field must be replaced with "Not Defined". Both the files have different numbers of rows.

Example:

file 1

a b c d e
a c d f
g f h j

File2

a b c d g
a c c b x

Output File

Name Value in file1 Value in file2
a b c d e g
a c d f Not Defined
g f h j Not Defined Not Defined
a c c c Not Defined x

Could somebody suggest a solution please. Many thanks in advance.

Welcome.

We won't do the homework for you but can help you past the hard parts. The GNU Core Utils package will likely have what you need. Can you please say what you've tried so far and which hints have been provided by your instructor?

Thank you very much for replying. I already have a working bash script which is incredibly slow as I am operating on many pairs of large files. In my solution I just looped through all lines of first file, examined corresponding value in second file ( if exists ) and printed the record in output file. Then I looped through all lines of second file and performed same steps. It would be extremely helpful to get suggestions on doing it more efficiently. Thank you once again.

Can you please show either your shell or AWK script so we can see where you are at in the process of finding a solution and propose improvements?

How should this be resolved?>