根據匹配的列數據連接兩個 CSV 文件

我想根據匹配的列資訊連接來自兩個 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"

這是一種解決方案，使用awk. 在 GNU awk 4.1.3 上測試。

$ 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行，並將它們添加到數組中，以欄位 #1 為鍵。
• 然後NR!=FNR匹配 file1 中的行。
• print $0","從 file1 列印整行，後跟逗號。
• (a[$6]?a[$6]:"\"\"")列印之前建構的數組的相應內容，但前提""是沒有找到條目。

引用自：https://unix.stackexchange.com/questions/521601