☰

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

tag-icon • tag-icon text-processing awk sed csv join
根據匹配的列資料連接兩個 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"

答案1

這是一種解決方案，使用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"
$
  • 符合NR==FNR{a[$1]=$2file2 中的行，並將它們新增到一個陣列中，鍵入欄位 #1。
  • 然後NR!=FNR匹配 file1 中的行。
  • print $0","列印 file1 中的整行，後面跟著一個逗號。
  • (a[$6]?a[$6]:"\"\"")列印之前建置的數組的相應內容，但前提""是沒有找到條目。

答案2

csvkit事實證明這比我預期的要難，但是使用基於 python 的1.0.2 版本中的工具：

$ csvjoin -HI -c 6,1 --left File1.csv File2.csv 2>/dev/null | csvformat -K1 -U1
"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"

（這-I似乎有必要防止csvjoin猜測點分隔欄位是日期時間，至少在我的語言環境中是如此。）

答案3

for i in `cat file2.csv | awk -F "," '{print $1}'| sed 's/"//g'`; do j=`sed -n '/'$i'/p' file2.csv| awk -F "," '{print $2}'| sed 's/"//g'`;awk -v i="$i" -v j="$j" -F "," '$NF==i{$7=j;print $0}' file1.csv; done| sed 's/ /","/g'| sed 's/^/"/g'| sed 's/$/"/g'

輸出

"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"

相關內容