在列值上,拆分 xlsx/csv文件

分享于 

5分钟阅读

电脑

  繁體 雙語

问题:

我的电子表格大约有 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和奥斯卡的所有字段等),但是我会非常满意单独按名称拆分。


回答 1:

尝试将它的分割为不同的文件:


$csv = Import-Csv C:tempexample.csv


$names = $csv | select -ExpandProperty Name -Unique


foreach ($name in $names) { $csv | where {$_.name -like $name} | export-csv c:tempexample_$name.csv }



可以再次执行这里操作,如果有帮助,将用组替换名称以分割每个结果文件


回答 2:

若要对输出进行分组,只需使用 Group-Object cmdlet 。


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





文件  BASE  COL  column  SPL  切分