☰

根据列值拆分 xlsx/csv 文件

tag-icon • tag-icon windows microsoft-excel powershell csv
根据列值拆分 xlsx/csv 文件

我知道这个问题之前有人问过，但之前的答案都不太好。我的电子表格总共有 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 "

相关内容