从特定列匹配输入文件的CSV中删除行

Question

我有一个包含多个列和行的CSV [File1.csv]。

我有另一个CSV文件（只有一列），列出一个特定的单词[File2.csv]。

如果任何列匹配File2中列出的任何单词，我希望能够删除File1中的行。

我最初使用这样的：

grep -v -F -f File2.csv File1.csv > File3.csv 

这个工作，在一定程度上。我遇到的这个问题是列中有多个字（例如word1，word2，word3）。 File2包含word2，但并未删除该行。

我累了把这些单词分开看起来像这样:(word1，word2，word3），但是原来的命令不起作用。

如何从File2中删除包含单词的行，并可能包含其他单词？

Answer 1

您可以在File2.csv中转换包含多个图案的分割线。

以下使用tr在将它们用作模式之前，将包含word1,word2的行转换为单独的行。所述<()构建临时充当文件/ FIFO（在bash测试）：采用awk

grep -v -F -f <(tr ',' '\n' < File2.csv) File1.csv > File3.csv 

Answer 2

一种方式。

内容script.awk：

BEGIN { 
    ## Split line with a doble quote surrounded with spaces. 
    FS = "[ ]*\"[ ]*" 
} 

## File with words, save them in a hash. 
FNR == NR { 
    words[ $2 ] = 1; 
    next; 
} 

## File with multiple columns. 
FNR < NR { 
    ## Omit line if eigth field has no interesting value or is first line of 
    ## the file (header). 
    if ($8 == "N/A" || FNR == 1) { 
     print $0 
     next 
    } 

    ## Split interested field with commas. Traverse it searching for a 
    ## word saved from first file. Print line only if not found. 

    ## Change due to an error pointed out in comments. 
    ##--> split($8, array, /[ ]*,[ ]*/) 
    ##--> for (i = 1; i <= length(array); i++) { 
    len = split($8, array, /[ ]*,[ ]*/) 
    for (i = 1; i <= len; i++) { 
    ## END change. 

     if (array[ i ] in words) { 
      found = 1 
      break 
     } 
    } 
    if (! found) { 
     print $0 
    } 
    found = 0 
} 

假设File1.csv和File2.csv已经托尔的答案的评论提供的内容（我建议加上这些信息的问题），运行像脚本：

awk -f script.awk File2.csv File1.csv 

With following output：

"DNSName","IP","OS","CVE","Name","Risk" 
"ex.example.com","1.2.3.4","Linux","N/A","HTTP 1.1 Protocol Detected","Information" 
"ex.example.com","1.2.3.4","Linux","CVE-2011-3048","LibPNG Memory Corruption Vulnerability (20120329) - RHEL5","High" 
"ex.example.com","1.2.3.4","Linux","CVE-2012-2141","Net-SNMP Denial of Service (Zero-Day) - RHEL5","Medium" 
"ex.example.com","1.2.3.4","Linux","N/A","Web Application index.php?s=-badrow Detected","High" 
"ex.example.com","1.2.3.4","Linux","CVE-1999-0662","Apache HTTPD Server Version Out Of Date","High" 
"ex.example.com","1.2.3.4","Linux","CVE-1999-0662","PHP Unsupported Version Detected","High" 
"ex.example.com","1.2.3.4","Linux","N/A","HBSS Common Management Agent - UNIX/Linux","High"