以下是输入:
文件1.csv
21|AAAAA|1023
21|BBBBB|1203
21|CCCCC|2533
22|DDDDD|1294
22|EEEEE|1249
22|FFFFF|4129
22A|GGGGG|4121
22A|HHHHH|1284
31B|IIIII|5403
31B|JJJJJ|1249
文件2.csv
21|A800
22|B900
22A|C1000
31B|D1000
预期输出:
文件3.csv
21|A800|AAAAA|1023
21|A800|BBBBB|1203
21|A800|CCCCC|2533
22|B900|EEEEE|1249
22|B900|FFFFF|4129
22A|C1000|GGGGG|4121
22A|C1000|HHHHH|1284
31B|D1000|IIIII|5403
31B|D1000|JJJJJ|1249
当前尝试使用连接,
join -a1 -t '|' -1 1 -2 1 -o 1.1,2.2,1.2,1.3 file1.csv file2.csv > file3.csv
但是它发现有些行缺少匹配,所以我将我的概念转向对这两个文件使用最有可能的 vlookup 功能。请帮忙。
谢谢大家
您能否尝试以下awk
,使用 GNU 编写和测试awk
显示的示例。
awk '
BEGIN{
FS=OFS="|"
}
FNR==NR{
arr[$1]=$2
next
}
($1 in arr){
$1=($1 OFS arr[$1])
}
1
' file2.csv file1.csv
说明:为上述添加详细说明。
awk ' ##Starting awk program from here.
BEGIN{ ##Starting BEGIN section from here of this program.
FS=OFS="|" ##Setting | as field separator and output field separator.
}
FNR==NR{ ##Checking condition if FNR==NR which will be TRUE when file2.csv is being read.
arr[$1]=$2 ##Creating arr with index of 1st field and value of 2nd field.
next ##next will skip all further statements from here.
}
($1 in arr){ ##checking condition if $1 is present in arr then do following.
$1=($1 OFS arr[$1]) ##Saving current $1 OFS and value of arr with index of $1 in $1.
}
1 ##1 will print the current line.
' file2.csv file1.csv ##Mentioning Input_file names here.
我测试了您提供的join
命令, 我认为它在我的机器上产生了预期的输出 (FreeBSD 12.2-RELEASE):
21|A800|AAAAA|1023
21|A800|BBBBB|1203
21|A800|CCCCC|2533
22|B900|DDDDD|1294
22|B900|EEEEE|1249
22|B900|FFFFF|4129
22A|C1000|GGGGG|4121
22A|C1000|HHHHH|1284
31B|D1000|IIIII|5403
31B|D1000|JJJJJ|1249
您可能需要首先对要连接的列(或者在这种情况下,当您加入第一列时,整行也应该工作)对两个文件进行排序,即join -a1 -t '|' -1 1 -2 1 -o 1.1,2.2,1.2,1.3 <(sort file1.csv) <(sort file2.csv) > file3.csv