トッカンソフトウェア

PowerShell CSVファイル関連

サンプルCSVファイル(testUTF8.csv)

				
a,b,c
a"aa,あああ,0
bbb,いいい,1
bbb,ううう,2

			

CSVファイルの読み込み

				
Set-Location  c:\work\hp
$csv = Import-Csv test.csv
Write-Output $csv

			

				
$csv = Import-Csv test.csv -Encoding Default
$csv = Import-Csv test.csv -Encoding OEM
$csv = Import-Csv testUTF8.csv -Encoding UTF8

			

CSVのデータを１行ごとにループ

				
Set-Location c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8

$clms = @("a", "b", "c");

# CSVファイルの１行ごとにループ
# １行目はカラム名になる
foreach ($data in $csv) {

    # .の後ろにカラム名で指定します。
    Write-Output $data.a

    # カラム名は変数でもOK
    foreach ($clm in $clms) {
        Write-Output $data.$clm
    }
}

# 上のループとやっていることは同じ
$csv | ForEach-Object {
    Write-Output $_.a

    # ForEachをネストする場合は、$_を別変数に格納します
    $obj = $_
    $clms | ForEach-Object {
        Write-Output $obj.$_
    }
}

			

ファイルに出力

				
Set-Location c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 

$csv | Export-Csv output1.csv -Encoding UTF8 -NoTypeInformation

$d = ','

$UTF8NoBom = New-Object System.Text.UTF8Encoding $False
$csvOut = $csv |  ConvertTo-Csv -Delimiter $d  -NoTypeInformation
$csvNoDq = $csvOut  | ForEach-Object { $_ -replace '^"', '' -replace "`"$d`"", $d -replace '"$', '' }

[System.IO.File]::WriteAllLines("c:\work\hp\output2.csv ", $csvNoDq, $UTF8NoBom)

$clms = $csv | Get-member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name'
$output = @()
foreach ($data in $csv) {
    $ary = @()
    foreach ($clm in $clms) {
        $ary += $data.$clm
    }
    $output += [string]::Join($d , $ary)
}

[System.IO.File]::WriteAllLines("c:\work\hp\output3.csv ", $output, $UTF8NoBom)

			

				
"a","b","c"
"a""aa","あああ","0"
"bbb","いいい","1"
"bbb","ううう","2"

			

				
a,b,c
a""aa,あああ,0
bbb,いいい,1
bbb,ううう,2

			

				
a"aa,あああ,0
bbb,いいい,1
bbb,ううう,2

			

先頭～、～末尾行のみ処理

				
Set-Location  c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 
$csvFirst = $csv | Select-Object -First 1
$csvLast = $csv | Select-Object -Last 1

$csvFirst | Format-Table
$csvLast | Format-Table

			

				
a    b   c
-    -   -
a"aa あああ 0

a   b   c
-   -   -
bbb ううう 2

			

ソート指定

				
Set-Location  c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 
$csvSort = $csv | Sort-Object -Property a, b -Descending
$csvSort | Format-Table

			

				
a    b   c
-    -   -
bbb  ううう 2
bbb  いいい 1
a"aa あああ 0

			

Where条件

				
Set-Location  c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 
$csvWhere = $csv | Where-Object { $_.c -ge 1 } 
$csvWhere | Format-Table

			

				
a   b   c
-   -   -
bbb いいい 1
bbb ううう 2

			

グループ化とグループごとの合計計算

				
Set-Location  c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 

$csvGroup = $csv | Group-Object a
$csvGroup  | Format-Table

$csvSum = $csv | Group-Object a | Select-Object Name,@{Name="c_sum"; Expression={($_.group | Measure-Object -sum c).Sum}}
$csvSum | Format-Table

			

				
Count Name                      Group                                                                                                                            
----- ----                      -----                                                                                                                            
    1 a"aa                      {@{a=a"aa; b=あああ; c=0}}                                                                                                          
    2 bbb                       {@{a=bbb; b=いいい; c=1}, @{a=bbb; b=ううう; c=2}}                                                                                     

Name c_sum
---- -----
a"aa     0
bbb      3

			

ヘッダなしの場合、ヘッダを付ける

				
Set-Location c:\work\hp
$csv = Import-Csv testUTF8.csv -Encoding UTF8 -Header "aa", "bb", "cc"
$csv | Format-Table

			

				
aa   bb  cc
--   --  --
a    b   c 
a"aa あああ 0 
bbb  いいい 1 
bbb  ううう 2 

			

区切り文字の指定

				
Set-Location c:\work\hp
$csv = Import-Csv testTab.csv -Encoding UTF8 -Delimiter "`t"
$csv | Format-Table