Shell script to filter records in a zip file that contains matching columns from another file

Shell Programming and Scripting
1

Not sure if this is the correct forum for this question. I have two files. file1.zip, file2

Input:

file1.zip

col1,    col2 ,   col3
a ,      b ,  0:0:0:0:0:c436:9346:d40b
x,       y,   0:0:0:0:0:880:39f9:c9a7
m,      n ,  0:0:0:0:0:80c7:9161:fe00

file2.txt

col1
c4:36:93:46:d4:0b
08:80:39:f9:c9:a7
48:1d:60:62:f5:9d

I need to

  • Convert col3 in file1 to col1 in file2 format i.e., ab:cd:ef:gh:ij:kl by padding 0's for and using semicolon':' for every 2 characters after trimming first 10 char.
  • compare it with col1 in table B and store the matching columns required output for the above scenario
col1 ,   col2,    col3
a  ,    b  , c4:36:93:46:d4:0b
x ,     y ,  08:80:39:f9:c9:a7

I have 1500 zip files and one reference file. Not sure how to achieve this in unix. Can someone please help.

for direct files, I used

ls *.zip | awk '{ print "zipgrep -f file2.txt "$0" >> result.csv"}' | xargs -I {}

2

With file1 unzipped, try

awk '    
FNR == NR       {T[$1]
                 next
                }
FNR == 1        {print
                 next
                }
                {n = split ($3, X, ":")
                 S = DL = ""
                 for (i=6; i<=n; i++)   {TMP = sprintf ("%d", "0X" X)
                                         S = S sprintf ("%s%02x:%02x", DL, TMP/256, TMP%256)
                                         DL = ":"
                                        }
                }
S in T          {$3 = S
                 print
                }
' FS="," OFS="," file2 file1
col1,    col2 ,   col3
a ,      b ,c4:36:93:46:d4:0b
x,       y,08:80:39:f9:c9:a7