CODEWITHSUNDEEP
awk
user3098729
user3098729

Reputation: 43

Matching data in separate files

I've seem several questions of this type and have truly tried to work this out on my own without success.

I have two files of data; I want to compare their first fields and print the line from the second file next to the matching entry from the first file in an output file. It is important to maintain the list order in file 1, including the lines with no matches. The files are tab separated values but I can change them if required (although commas are in use in the data).

file1.txt

37600
39219
32887
01262
69241
00361
34180
42385
69245

file2.txt

37600       GEAR PUMP   1
32887       MOTOR, 480V 1
34180       BRACKET 2
00361       WASHER  4
42385       SCREW       8

Desired Output file

37600       GEAR PUMP   1
39219
32887       MOTOR, 480V 1
01262
69241
00361       WASHER  4
34180       BRACKET 2
42385       SCREW       8
69245

This is my best effort so far but it results in an empty file.

awk NR==NFR{a[$1];next}$1 in a {print $0} file1.txt file2.txt > outfile.txt

Any help would be greatly appreciated.

Upvotes: 4

Views: 141

Answers (2)

zx8754
zx8754

Reputation: 56239

Using join:

join -1 2 -2 1 -a1 <(cat -n file1.txt | sort -k2,2) <(sort file2.txt) | sort -k2 | cut --complement -d" " -f2

37600 GEAR PUMP 1
39219
32887 MOTOR, 480V 1
01262
69241
00361 WASHER 4
34180 BRACKET 2
42385 SCREW 8
69245

As per @devnul's suggestion, thanks.

Upvotes: 2

Jotne
Jotne

Reputation: 41460

You were close, this awk should do.

awk 'FNR==NR {a[$1]=$0;next} a[$0] {$0=a[$0]}1' file2 file1
37600       GEAR PUMP   1
39219
32887       MOTOR, 480V 1
01262
69241
00361       WASHER  4
34180       BRACKET 2
42385       SCREW       8
69245

I stores the file2 in array with $1 as an index.
If value in file1 is found in array, set this as $0 and print all out.

You could write this too, to mark that its column #1 that should be compared:

awk 'FNR==NR {a[$1]=$0;next} a[$1] {$0=a[$1]}1' file2 file1

Upvotes: 3

Related Questions