![根据列值拆分 xlsx/csv 文件](https://linux22.com/image/1585472/%E6%A0%B9%E6%8D%AE%E5%88%97%E5%80%BC%E6%8B%86%E5%88%86%20xlsx%2Fcsv%20%E6%96%87%E4%BB%B6.png)
我知道这个问题之前有人问过,但之前的答案都不太好。我的电子表格总共有 30000 行。
我的电子表格格式如下:
Name,colour,postcode,Easting,Northing,type,Group
123,red,1,1,1,1,oscar
123,blue,1,1,1,1,bravo
122,magenta,1,1,1,1,oscar
122,turquoise,1,1,1,1,oscar
121,brown,1,1,1,1,bravo
121,green,1,1,1,1,oscar
我需要根据名称字段拆分文件。如果我可以按名称和组拆分(例如,一个文件中包含 121 和 oscar 的所有字段等),那就更好了,但我很乐意只按名称拆分。
答案1
尝试将其拆分成不同的文件:
$csv = Import-Csv C:\temp\example.csv
$names = $csv | select -ExpandProperty Name -Unique
foreach ($name in $names) { $csv | where {$_.name -like $name} | export-csv c:\temp\example_$name.csv }
您可以再做一次,用组替换名称以拆分每个结果文件(如果有帮助)
答案2
要对输出进行分组,只需使用 cmdlet Group-Object
。
> Import-Csv .\example.csv | Group-Object Name,Group | Sort-Object Name
Count Name Group
----- ---- -----
1 121, bravo {@{Name=121; colour=brown; postcode=1; Easting=1; Northing=1; type=1; Group=bravo }}
1 121, oscar {@{Name=121; colour=green; postcode=1; Easting=1; Northing=1; type=1; Group=oscar}}
2 122, oscar {@{Name=122; colour=magenta; postcode=1; Easting=1; Northing=1; type=1; Group=oscar }, @{Name=12...
1 123, bravo {@{Name=123; colour=blue; postcode=1; Easting=1; Northing=1; type=1; Group=bravo}}
1 123, oscar {@{Name=123; colour=red; postcode=1; Easting=1; Northing=1; type=1; Group=oscar }}
请注意,Group-Object
输出自己的固定属性Count,Name,Group
(与你的属性名称。)
这里的缺点是自动生成的Name
属性包含逗号
(不能直接用于输出文件名)
但是您可以动态创建类似于计算属性的分组。
> Import-Csv .\example.csv | Group-Object {$_.Name+'_'+$_.Group.Trim(' ')}| Sort-Object Name
Count Name Group
----- ---- -----
1 121_bravo {@{Name=121; colour=brown; postcode=1; Easting=1; Northing=1; type=1; Group=bravo }}
1 121_oscar {@{Name=121; colour=green; postcode=1; Easting=1; Northing=1; type=1; Group=oscar}}
2 122_oscar {@{Name=122; colour=magenta; postcode=1; Easting=1; Northing=1; type=1; Group=oscar }, @{Name=12...
1 123_bravo {@{Name=123; colour=blue; postcode=1; Easting=1; Northing=1; type=1; Group=bravo}}
1 123_oscar {@{Name=123; colour=red; postcode=1; Easting=1; Northing=1; type=1; Group=oscar }}
要将组输出到speaking
文件名:
Import-Csv .\example.csv | Group-Object {$_.Name+'_'+$_.Group.Trim(' ')} | Sort-Object Name |
ForEach-Object{
$File = "example_{0}.csv" -f $_.Name
$_.Group | Export-Csv -Path $File -NoTypeInformation
}
示例结果:
> gci example_*.csv | %{'_'*10+$_.Name+'_'*10;gc $_.Name;""}
__________example_121_bravo.csv__________
"Name","colour","postcode","Easting","Northing","type","Group"
"121","brown","1","1","1","1","bravo "
__________example_121_oscar.csv__________
"Name","colour","postcode","Easting","Northing","type","Group"
"121","green","1","1","1","1","oscar"
__________example_122_oscar.csv__________
"Name","colour","postcode","Easting","Northing","type","Group"
"122","magenta","1","1","1","1","oscar "
"122","turquoise","1","1","1","1","oscar "
__________example_123_bravo.csv__________
"Name","colour","postcode","Easting","Northing","type","Group"
"123","blue","1","1","1","1","bravo"
__________example_123_oscar.csv__________
"Name","colour","postcode","Easting","Northing","type","Group"
"123","red","1","1","1","1","oscar "