Shell Programming and Scripting

Hi, I have been looking everywhere for an example so I can try and do this myself but I am having difficulty. I have 2 large files of different sizes and if the pattern in the 3rd column in file 1 is in "any" column in file 2 I want to print all of the line in file 1 and append that line with the pattern in column 1 of file 2.

Here is an example. The files are comma separated.

file 1

Code:

A,B,XXX,000
C,D,YYY,111
E,F,ZZZ,222

file 2

Code:

G,XXX,333,H,222
H,4,555,YYY,kkk
L,R,JJJ,LMN,KRP,ZZZ,0

In this example, in file 1 I want to look for XXX, YYY and ZZZ in file 2 which could be in any column. When I find them I want to print the line from file 1 and the entry in the 1st column in file 2. Like this

Code:

A,B,XXX,000,G
C,D,YYY,111,H
E,F,ZZZ,222,L

I have tried several variations of what I have seen in NR==FNR posts but I am finding it hard to get the right combination. All the NR==FNR posts talk about matching a "specific" column in one file with a "specific" column in another file.

I would really really appreciate help with this.

Thanks in advance.

Hi,
In file 1 or 2, can we find several times the same pattern ?
Can we load in memory file 1 or is it too large ?
Regards.

Well, try

Code:

awk 'NR==FNR {T[$3]=$0; next} {for (t in T) if ($0 ~ t) print T[t], $1}' FS=, OFS=, file[12]
A,B,XXX,000,G
C,D,YYY,111,H
E,F,ZZZ,222,L

There are 2445 entries in file 1 and 2431 entries in file 2. File 1 is 264KB and file 2 is 352 KB. file 2 has a lot more columns.

The pattern in column 3 in file 1 can exist in several lines in file 2 but only once in file 1. So I need to read in column 3 in every line from file 1 and look for a match in every line in file 2. And also the pattern in column 3 in file 1 can also "not" exist in file 2 and then this happens I want to just print the line from file 1 with nothing appended.

RudiC, Can I put an else in your code to print the line from file 1 when no matches occur? Something like so?

Code:

awk 'NR==FNR {T[$3]=$0; next} {for (t in T) if ($0 ~ t) {print T[t], $1} else {print T[t]}}' FS=, OFS=, file1 file2

More like

Code:

awk 'NR==FNR {T[$3]=$0; P[$3]; next} {for (t in T) if ($0 ~ t) {print T[t], $1; delete P[t]}} END {for (p in P) print T[p]}' FS=, OFS=, file[12]

---------- Post updated at 16:39 ---------- Previous update was at 15:10 ----------

Above has a drawback: Although it wouldn't match substrings of file1's $3 in file2, superstrings would be matched. If that's a problem, we need to reconsider.

Better loop over the fields, and the look-up in the array is exact and maybe even faster.

Code:

awk 'NR==FNR {T[$3]=$0; next} {for (i=1;i<=NF;i++) if ($i in T) print T[$i], $1}' FS=, OFS=, file[12]

---------- Post updated at 03:01 PM ---------- Previous update was at 02:29 PM ----------

"break" the loop if found once, and with the latest requirement

Code:

awk 'NR==FNR {T[$3]=$0; next} {for (i=1;i<=NF;i++) if ($i in T) {print T[$i], $1; seen[$i]; break}} END {for (t in T) if (!(t in seen)) print T[t]}' FS=, OFS=, file1 file2