Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two files

file1.txt

78Z|033333157|0000001|PERD1|2150421|D|0507020|3333333311
78Z|033333157|0000001|PERD0|2160208|A|1900460|3333333311
78Z|033333157|0000001|RSAB1|2150421|D|0507070|3333333311
78Z|033333157|0000001|RSAB0|2160208|A|1900460|3333333311
78Z|033333157|0000001|ANT37|2141023|D|1245260|3333333311
78Z|033333157|0000001|ANT36|2150422|D|1518490|3333333311
78Z|033333157|0000001|ANT28|2150321|D|0502090|3333333311
78Z|033333157|0000001|ANT27|2150122|D|0501450|3333333311
78Z|033333157|0000001|ANT26|2141222|D|1637460|3333333311
78Z|033333157|0000001|ANT10|2160208|A|1900460|3333333311
78Z|033333157|0000001|ABS10|2151221|D|1223390|3333333311
78Z|696931836|0000001|PERD0|2160203|A|1114450|2222222222
78Z|696931836|0000001|RSAB0|2160203|A|1114450|2222222222
78Z|696931836|0000001|ANT09|2160203|A|1114450|2222222222
78Z|010041586|0000001|PERD0|2160119|A|1835100|3333333333
78Z|010041586|0000001|RSAB0|2160119|A|1835100|3333333333
78Z|010041586|0000001|ANT33|2160119|A|1835100|3333333333
78Z|011512345|0000001|PERD0|2151213|A|1413550|4444444444
78Z|011512345|0000001|RSAB0|2151213|A|1413550|4444444444
78Z|011512345|0000001|ANT32|2160219|A|0319230|4444444444
78Z|011512345|0000001|ANT09|2160218|D|0319230|4444444444
78Z|011512345|0000001|ANT07|2150729|D|1508230|4444444444
78Z|011512345|0000001|ANT06|2141013|D|1208190|4444444444
78Z|011512345|0000001|ABB06|2131224|D|1857030|4444444444
78Z|012344052|0000001|PERD0|2160203|A|1219570|5555555555
78Z|012344052|0000001|ANT50|2160203|A|1219570|5555555555
78Z|099999999|0000001|PERD0|2151214|A|1512460|6666666666
78Z|099999999|0000001|RSAB0|2151214|A|1512460|6666666666
78Z|099999999|0000001|ANT32|2160219|A|0319000|6666666666
78Z|099999999|0000001|ANT09|2160218|D|0319000|6666666666
78Z|099999999|0000001|ABS10|2150615|D|0125350|6666666666

file2.txt

3333333311|ANT10
2222222222|ANT09
5555555555|ANT50
3333333333|ANT33
6666666666|ANT32
4444444444|ANT09

I need a create new file with the lines matched by fourth and eighth column of the file1.txt with second and first column of the file2.txt

The result must be (the order is not important)

file3.txt

78Z|033333157|0000001|ANT10|2160208|A|1900460|3333333311
78Z|696931836|0000001|ANT09|2160203|A|1114450|2222222222
78Z|012344052|0000001|ANT50|2160203|A|1219570|5555555555
78Z|010041586|0000001|ANT33|2160119|A|1835100|3333333333
78Z|099999999|0000001|ANT32|2160219|A|0319000|6666666666
78Z|011512345|0000001|ANT09|2160218|D|0319230|4444444444
share|improve this question
    
Please improve your question. You don't ask something. And please specify that you want and answer with awk: apart from the "awk" tag you used, we don't see that... – Totor Feb 22 at 22:19
    
@Totor - the post says "I need to create new file with the lines...." and "the result must be..." Isn't that enough ? And why should OP specify the answer needs to be awk ? – don_crissti Feb 22 at 22:42
    
@don_crissti if the OP uses the tag "awk" on it's question, I expect to discover why when I read the question, but here, I don't know. I think this question can be improved with a tiny effort. Just saying this because, as you may have noticed, there is a close vote on it "unclear what you're asking". – Totor Feb 23 at 15:01
    
@Totor - no. It's perfectly fine - there are thousands of questions tagged awk or sed or perl and the authors do NOT explain why - most times because they don't even know which tool is right one. Also, I'm not sure what exactly is unclear, especially after all the edits - it was pretty clear to me even before the edits. – don_crissti Feb 23 at 15:09
    
Thank you for your all comments, I forget specifc tag of the question, the answerd must be in awk, I working with awk. – Miguel Angel Feb 24 at 15:44
up vote 5 down vote accepted
awk -F'|' 'NR==FNR{e[$2$1]=1;next};e[$4$8]' file2.txt file1.txt

First read file2 and set array e[field2+field1] then file1 and print if e[field4+field8] is set.

Or turn the fields around:

awk -F'|' 'NR==FNR{e[$1$2]=1;next};e[$8$4]' file2.txt file1.txt
share|improve this answer
    
Thank you @Sukminder, the instruction works correctly!!!! – Miguel Angel Feb 22 at 21:36

The question is tagged , but I think its worth noting that you don't have to use awk to do this. The coreutils have a join utility which is useful for this sort of thing, though with your data it is a bit cumbersome.

I'm going to assume your shell is bash and GNU coreutils. This can be done with other flavours, but will probably need more pre-/post-processing of your data.

Without further ado:

join -t"|" -14 -22 -o"$(echo 1.{1..8} 2.1)" \
  <(sort -t"|" -k4 file1.txt) \
  <(sort -t"|" -k2 file2.txt) \
  | grep -Po ".*([\d]+)(?=\|\1$)"

join takes two input files and joins them on the fields passed with the -1 and -2 parameters. -t indicates a custom field separator in the input files. join requires that the input files are sorted on the fields being joined, so the two input files are sorted within process substitutions before being passed to join. The -o parameter specifies how fields are to be output. By default it is join field, followed by non-join fields of file1, followed by non-join fields of file2, so we need to specify we want all fields of file 1 in order, followed by field 1 of file 2.

You also require the join to be on field 8 of file1 and field 1 of file1. This is simply implemented here by grepping for lines when the join output fields 8 and 9 are the same. Using a regexp lookahead with grep -Po allows us to do this and effectively delete field 9 to give the required output. The output is:

78Z|696931836|0000001|ANT09|2160203|A|1114450|2222222222
78Z|011512345|0000001|ANT09|2160218|D|0319230|4444444444
78Z|033333157|0000001|ANT10|2160208|A|1900460|3333333311
78Z|099999999|0000001|ANT32|2160219|A|0319000|6666666666
78Z|010041586|0000001|ANT33|2160119|A|1835100|3333333333
78Z|012344052|0000001|ANT50|2160203|A|1219570|5555555555
share|improve this answer
1  
In case it's useful to anyone, here's a portable alternative to the grep component: sed -n 's/\([^|]\{1,\}\)|\1$/\1/p' – Barefoot IO Feb 23 at 3:05
    
@Digital Trauma Thanks for answer, however I request de code in awk, your code is works perfect, use join is another solution for my request. – Miguel Angel Feb 24 at 15:49

try

awk 'BEGIN { FS="|"}
     FNR == NR { key[$2 $1]=NR }
     FNR > NR { k=$3 $8 ;if (k in key)  print ;} ' file2.txt file1.txt

where

  • BEGIN { FS="|"} tell aw to use | as separator (you can use -F\| on command line )
  • FNR == NR { key[$2 $1]=NR } remember key from file (when in file2.txt)
  • FNR > NR { k=$4 $8 ;if (k in key) print ;} if key in hash list, print it

Edit: you mention third filed and join on fourth.

share|improve this answer
    
FYI, since you never use the values stored in the key array, you can just omit the value assignment. key[$2 $1] will create the array element just as well and is a lot tidier. – Wildcard Feb 22 at 20:44

Simpler, assuming that in file2 it is not allowed to give the same first field twice:

awk -F'|' 'FNR == NR { key[$1] = $2; next } $8 in key && key[$8] == $4' file2.txt file1.txt
share|improve this answer
    
Thank you my friend @Wildcard – Miguel Angel Feb 23 at 0:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.