一致する列情報に基づいて2つのCSVファイルのデータを結合したいと思います。照合するデータは、File1.csv 列 5 と File2 列 1 から得られます。一致するときにFile2列2の情報を追加し、一致しない場合は空の二重引用符を残したいと思います。
ファイル1.csv
"Z","P","W","K","1","1.18.24.59"
"S","K","D","X","9","1.14.19.238"
"R","M","P","Y","8","1.15.11.21"
"B","D","0","U","5","1.9.20.159"
"R","E","W","Q","6","135.0.0.1"
"K","D","K","R","9","1.9.74.13"
ファイル2.csv
"65.9.7.19","374 22 53"
"1.9.74.13","123 256 51"
"1.18.24.59","23 25 41"
"1.15.11.21","98 77 8291"
"1.14.19.238","8827 145 8291"
"1.9.20.159","283 1 5734"
希望の出力
"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"
ベストアンサー1
次はawk。
$ awk -F, 'NR==FNR{a[$1]=$2}NR!=FNR{print $0","(a[$6]?a[$6]:"\"\"")}' file2.csv file1.csv
"Z","P","W","K","1","1.18.24.59","23 25 41"
"S","K","D","X","9","1.14.19.238","8827 145 8291"
"R","M","P","Y","8","1.15.11.21","98 77 8291"
"B","D","0","U","5","1.9.20.159","283 1 5734"
"R","E","W","Q","6","135.0.0.1",""
"K","D","K","R","9","1.9.74.13","123 256 51"
$
- file2の行を一致さ
NR==FNR{a[$1]=$2せて配列に追加し、field #1と入力します。 - 次に、
NR!=FNRfile1の行を一致させます。 print $0","file1の行全体を印刷し、その後にカンマを付けます。(a[$6]?a[$6]:"\"\"")以前に構成された配列の対応する内容を印刷します。ただし、""項目がない場合にのみ印刷されます。