根据bash / posh中数组中的列匹配值将CSV拆分为两个文件
根据bash / posh中数组中的列匹配值将CSV拆分为两个文件
提问于 2019-11-14 07:17:38
我有一个输入CSV，我希望将其拆分为两个CSV文件。
WLTarray：
"22532" "79994" "18809" "21032"输入CSV文件：
header1,header2,header3,header4,header5,header6,header7,header8
"83","6344324","585677","22532","Entitlements","BX","22532:718","36721"
"83","1223432","616454","79994","Compliance Stuff","DR","79994:64703","206134"
"83","162217","616454","83223","Data Enrichment","IEO","83223:64701","206475"
"83","267216","616457","79994","Compliance Engine","ABC","79994:64703","206020"输出CSV file1：
header1,header2,header3,header4,header5,header6,header7,header8
"83","6344324","585677","22532","Entitlements","BX","22532:718","36721"
"83","1223432","616454","79994","Compliance Stuff","DR","79994:64703","206134"
"83","267216","616457","79994","Compliance Engine","ABC","79994:64703","206020"输出CSV file2：
header1,header2,header3,header4,header5,header6,header7,header8
"83","162217","616454","83223","Data Enrichment","IEO","83223:64701","206475"我一直在寻找awk来过滤这个(python和perl在我的环境中不是一个选项)，但我认为可能有一种更聪明的方法：
declare -a WLTarray=("22532" "79994" "18809" "21032")
for WLTvalue in "${WLTarray[@]}" #Everything in the WLTarray will go to $filename-WLT.tmp
do
awk -F, '($4=='$WLTvalue'){print}' $filename.tmp >> $filename-WLT.tmp #move the lines to the WLT file
# now filter to remove non matching values? why not just move the rows entirely?
done回答 3
Stack Overflow用户
发布于 2019-11-14 09:17:47
对于常规的awk，您可以使用split和substr (处理去掉双引号以进行比较)，并按照您的指示拆分csv文件。例如，您可以使用：
awk 'BEGIN { FS=","; s="22532 79994 18809 21032"
split (s,a," ") # split s into array a
for (i in a) # loop over each index in a
b[a[i]]=1 # use value in a as index for b
}
FNR == 1 { # first record, write header to both output files
print $0 > "output1.csv"
print $0 > "output2.csv"
next
}
substr($4,2,length($4)-2) in b { # 4th field w/o quotes in b?
print $0 > "output1.csv" # write to output1.csv
next
}
{ print $0 > "output2.csv" } # otherwise write to output2.csv
' input.csv其中：
- 在
BEGIN {...}规则中，将字段分隔符(FS)设置为逗号换行，并将包含所需output1.csv字段4个匹配项的字符串拆分到数组a中，然后循环a中的值，使用它们作为数组b中的索引(以允许简单的i in b检查)； - 第一条规则应用于文件(标题行)中的第一条记录，该记录只是写到两个输出文件中；
- 下一条规则删除字段-4两边的双引号，然后检查字段-4中的数字是否与数组
b中的索引匹配。如果是，则将记录写入output1.csv，否则将其写入output2.csv。
输入文件示例
$ cat input.csv
header1,header2,header3,header4,header5,header6,header7,header8
"83","6344324","585677","22532","Entitlements","BX","22532:718","36721"
"83","1223432","616454","79994","Compliance Stuff","DR","79994:64703","206134"
"83","162217","616454","83223","Data Enrichment","IEO","83223:64701","206475"
"83","267216","616457","79994","Compliance Engine","ABC","79994:64703","206020"结果输出文件
$ cat output1.csv
header1,header2,header3,header4,header5,header6,header7,header8
"83","6344324","585677","22532","Entitlements","BX","22532:718","36721"
"83","1223432","616454","79994","Compliance Stuff","DR","79994:64703","206134"
"83","267216","616457","79994","Compliance Engine","ABC","79994:64703","206020"
$ cat output2.csv
header1,header2,header3,header4,header5,header6,header7,header8
"83","162217","616454","83223","Data Enrichment","IEO","83223:64701","206475"Stack Overflow用户
发布于 2019-11-14 08:23:34
您可以像这样使用gawk：
test.awk
#!/usr/bin/gawk -f
BEGIN {
split("22532 79994 18809 21032", a)
for(i in a) {
WLTarray[a[i]]
}
FPAT="[^\",]+"
}
NR > 1 {
if ($4 in WLTarray) {
print >> "output1.csv"
} else {
print >> "output2.csv"
}
}让它成为可执行文件，并像这样运行它：
chmod +x test.awk
./test.awk input.csvStack Overflow用户
发布于 2019-11-14 09:10:03
使用grep和过滤器文件作为输入是最简单的答案。
declare -a WLTarray=("22532" "79994" "18809" "21032")
for WLTvalue in "${WLTarray[@]}"
do
awkstring="'\$4 == "\"\\\"$WLTvalue\\\"\"" {print}'"
eval "awk -F, $awkstring input.csv >> output.WLT.csv"
done
grep -v -x -f output.WLT.csv input.csv > output.NonWLT.csv页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58847074
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号