☰

根據列值拆分 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 "

相關內容