-1

I have two comma-separated csv files (section_ne_lookup.csv and section_fru_out.csv):

    -sh-4.1$ cat section_ne_lookup.csv
CIBI_NMS_NE,,,,,,,,,,,,,,,,,,,,,,,
D,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u,v,w
X,NE1,1.1.1.1,,833-006355,00,Windows,,,9.4.04,301035,,,,,,,,,,,,,
X,NE2,2.2.2.2,,833-006352,00,XS-A,,,5.1,51.573,,,,,,,,,,,,,
X,80,3.3.3.3,,833-006366,00,XS-F,,,5.2,52.604,,,,,,,,,,,,,
X,83,4.4.4.4,,833-006366,00,XS-F,,,5.3,53.575,,,,,,,,,,,,,
X,85,5.5.5.5,,833-006352,00,XS-A,,,5.3,53.605,,,,,,,,,,,,,
X,87,6.6.6.6,,Dummy_EPLC60,Dummy_EPLR60,XS-A,,,5.3,53.543,,,,,,,,,,,,,

-sh-4.1$ cat section_fru_out.csv
CIBI_NMS_FRU,,,,,,,,,,,,,,,,,,,,,,,
D,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u,v,w,x,y
X,UNKNOWN,0,NE2,,,,NE2:klm11,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm12,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm13,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm14,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm15,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm16,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm2,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm3,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm4,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm5,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm6,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm7,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm8,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,NE2,,,,NE2:klm9,,,,UNKNOWN,,,,,,,0,,,,,,
X,abc,0,83,,,,83:klm1,,,,rty,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm2,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm3,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm4,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm5,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm6,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm7,,,,UNKNOWN,,,,,,,0,,,,,,
X,UNKNOWN,0,80,,,,80:klm8,,,,UNKNOWN,,,,,,,0,,,,,,
X,TBD,1,NE2,,,,NE2:klm4.asd1,,,,bnm,,,,,,,0,,,,,,0000
X,TBD,1,80,,,,80:.klm1.asd1,,,,bnm,,,,,,,0,,,,,,3132
X,abc,1,87,,,,87:ab1,,,,,,,,,,,0,,,,,,xyz-C
X,abc,0,87,,,,87:abA,,,,bnm,,,,,,,0,,,,,,xyz-A

What I want to do is: If the 5th or 6th column of section_ne_lookup.csv starts with "Dummy_" phrase, fetch the value of 2nd column of matching line. And check if this fetched value (2nd column of the matching line in section_ne_lookup.csv) is present in the 4th column of section_fru_out.csv. If yes, remove the matching line from section_fru_out.csv. If no, keep the matching line in section_fru_out.csv. Based on this rule, following lines should be removed:

X,abc,1,87,,,,87:ab1,,,,,,,,,,,0,,,,,,xyz-C
X,abc,0,87,,,,87:abA,,,,bnm,,,,,,,0,,,,,,xyz-A

Because, 5th and 6th columns of the last 2 lines in section_ne_lookup.csv starts with Dummy_ phrase. Hence, we should fetch the second column (87 in this case) of the matching line and search for this value (87) in 4th column of the file: section_fru_out.csv. Since the last 2 lines of this file matches this rule, these lines should be removed.

How can I do that?

closed as unclear what you're asking by Kusalananda, Thor, DarkHeart, Rui F Ribeiro, Anthon Apr 27 '17 at 20:31

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What's the "related line"? And how can the two lines you show be removed? They don't actually exist in either input file. – terdon Apr 27 '17 at 9:46
  • Edited the description. I hope it is clear now. – Murat Apr 27 '17 at 11:05
  • Not really. You keep referring to "the matching line" but don't explain how we are supposed to match. How can we know what the matching line is? And you are still showing output that doesn't exist in your input. Where does this 3FE68863 come from? – terdon Apr 27 '17 at 11:08
  • Sorry, I updated the output. In short, 2nd column of section_ne_lookup.csv and 4th column of section_fru_out.csv belongs to same attribute - NE Name. If the 5th or 6th column of any of the records in section_ne_lookup.csv starts with Dummy_ phrase, fetch the NE name (2nd column) of this record. I do not want to have any record which belongs to this NE in section_fru_out.csv. The fetched value should be searched in 4th column of section_fru_out.csv and if it exists, the record which is having the fetched value in its 4th column should be removed from section_fru_out.csv. – Murat Apr 27 '17 at 11:23
  • Please edit your question to add extra information. Comments are easy to miss, hard to read and can be deleted without warning. That said, does my solution below work for you? – terdon Apr 27 '17 at 11:26
2

This should do what you need:

awk -F, '{
            if(NR==FNR){a[$2]=$5; b[$2]=$6;}
            else if(a[$4] !~ /^Dummy/ && b[$4] !~ /^Dummy/){print}
        }' section_ne_lookup.csv section_fru_out.csv 
  • if(NR==FNR){a[$2]=$5; b[$2]=$6;} : FNR is the line number of the current input file and NR is the line number of all input. When they are equal, we are reading the first file. Then, we make a and b associative arrays whose keys are the second fields and whose values are the 5th and 6th respectively.

  • else if (a[$4] !~ /^Dummy/ && b[$4] !~ /^Dummy/){print} : If this isn't the 1st file and if neither the 4th nor the 5th field of the line associated with the 2nd field in section_ne_lookup.csv start with Dummy, then print the line.

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