Home > Net >  AWK : comparing 2 columns from 2 csv files, outputting to a third. How do I also get the output that
AWK : comparing 2 columns from 2 csv files, outputting to a third. How do I also get the output that

Time:12-01

I currently have the following script: awk -F, 'NR==FNR { a[$1 FS $4]=$0; next } $1 FS $4 in a { printf a[$1 FS $4]; sub($1 FS $4,""); print }' file1.csv file2.csv > combined.csv

this compares two columns 1 & 4 from both csv files and outputs the result from both files to combined.csv. Is it possible to output the lines from file 1 & file 2 that dont match to other files with the same awk line? or would i need to do seperate parses?

File1
ResourceName,ResourceType,PatternType,User,Host,Operation,PermissionType
BIG.TestTopic,Cluster,LITERAL,Bigboy,*,Create,Allow
BIG.PRETopic,Cluster,LITERAL,Smallboy,*,Create,Allow
BIG.DEVtopic,Cluster,LITERAL,Oldboy,*,DescribeConfigs,Allow

File2
topic,groupName,Name,User,email,team,contact,teamemail,date,clienttype
BIG.TestTopic,BIG.ConsumerGroup,Bobby,Bigboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer
BIG.DEVtopic,BIG.ConsumerGroup,Bobby,Oldboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer
BIG.TestTopic,BIG.ConsumerGroup,Susan,Younglady,[email protected],team 1,Susan,[email protected],2021-11-26T10:10:17Z,Producer

combined
BIG.TestTopic,Cluster,LITERAL,Bigboy,*,Create,Allow,BIG.TestTopic,BIG.ConsumerGroup,Bobby,Bigboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer
BIG.DEVtopic,Cluster,LITERAL,Oldboy,*,DescribeConfigs,Allow,BIG.DEVtopic,BIG.ConsumerGroup,Bobby,Oldboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer

Wanted additional files:

non matched file1:
BIG.PRETopic,Cluster,LITERAL,Smallboy,*,Create,Allow

non matched file2:
BIG.TestTopic,BIG.ConsumerGroup,Susan,Younglady,[email protected],team 1,Susan,[email protected],2021-11-26T10:10:17Z,Producer```

again, I might be trying to do too much in one line? would it be wiser to run another parse?

CodePudding user response:

Assuming the key pairs of $1 and $4 are unique within each input file then using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN { FS=OFS="," }
FNR==1 { next }
{ key = $1 FS $4 }
NR==FNR {
    file1[key] = $0
    next
}
key in file1 {
    print file1[key], $0 > "out_combined"
    delete file1[key]
    next
}
{
    print > "out_file2_only"
}
END {
    for (key in file1) {
        print file1[key] > "out_file1_only"
    }
}

$ awk -f tst.awk file{1,2}

$ head out_*
==> out_combined <==
BIG.TestTopic,Cluster,LITERAL,Bigboy,*,Create,Allow,BIG.TestTopic,BIG.ConsumerGroup,Bobby,Bigboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer
BIG.DEVtopic,Cluster,LITERAL,Oldboy,*,DescribeConfigs,Allow,BIG.DEVtopic,BIG.ConsumerGroup,Bobby,Oldboy,[email protected],team 1,Bobby,[email protected],2021-11-26T10:10:17Z,Consumer

==> out_file1_only <==
BIG.PRETopic,Cluster,LITERAL,Smallboy,*,Create,Allow

==> out_file2_only <==
BIG.TestTopic,BIG.ConsumerGroup,Susan,Younglady,[email protected],team 1,Susan,[email protected],2021-11-26T10:10:17Z,Producer

The order of lines in out_file1_only will be shuffled by the in operator - if that's a problem let us know as it's an easy tweak to retain the input order.

  • Related