📚

PowerShell:CSVをSQLで結合してみる

2021/03/19に公開

SQL

PowerShell

CSV

tech

これで出力されたCSVを結合処理したかったので、PowerSehellでCSVファイルをSQL処理する方法を調べてみた。

CSVデータ

文字コードは、Shift_JISでやってます。
(function内でUTF-8にも対応可能）

読み込むCSV その1

UserOU.csv

"SamAccountName","DisplayName","DistinguishedName"
"test-user01","テストユーザー01","testOU01"
"test-user02","テストユーザー02","testOU02"
"test-user03","テストユーザー03","testOU03"

読み込むCSV その2

Member.csv

"SamAccountName","MemberOf1","MemberOf2","MemberOf3"
"test-user01","sec-member01","sec-member02","sec-member03"
"test-user02","sec-member02","sec-member03",""

実行結果

JoinUserDate.csv

"SamAccountName","DisplayName","DistinguishedName","MemberOf1","MemberOf2","MemberOf3"
"test-user01","テストユーザー01","testOU01","sec-member01","sec-member02","sec-member03"
"test-user02","テストユーザー02","testOU02","sec-member02","sec-member03",""
"test-user03","テストユーザー03","testOU03","","",""

完成したスクリプト

Join-Csv.ps1

# Join-Csvのfunctionを定義
function Join-Csv
{
  Param
  (
    [Parameter(Mandatory=$true,ValueFromPipeline=$true)] 
    [String] $Query,

    [ValidateNotNullOrEmpty()]
    [String] $Path = (Get-Location).Path,

    # [Text.Encoding]::GetEncodings() | % Name
    [ValidateSet('UTF-8', 'Shift_JIS')]
    [String] $Encoding = 'Shift_JIS',

    [ValidateNotNull()]
    [Switch] $NoHead
  )

  Begin
  {
    $close = {
      if ($Connection -ne $null)
      {
        $Connection.Close()
        $Connection.Dispose()
      }
    }

    [void][System.Reflection.Assembly]::LoadWithPartialName("System.Data")
    $Connection  = New-Object 'System.Data.OleDb.OleDbConnection'
    $Cmd         = New-Object 'System.Data.OleDb.OleDbCommand'
    $DataAdapter = New-Object 'System.Data.OleDb.OleDbDataAdapter'
    $DataSet     = New-Object 'System.Data.DataSet'

    $Path = Resolve-Path $Path
    $HDR = if ($NoHead) {'NO'} else {'YES'}
    $CharacterSet = [Text.Encoding]::GetEncoding($Encoding).CodePage

    $ExtendedProperties = @(
      "Text"
      "HDR=${HDR}"
      "FMT=Delimited"
      "CharacterSet=${CharacterSet}"
    ) -join ";"

    $Connection.ConnectionString = @(
      "Provider=Microsoft.Jet.OleDb.4.0",
      "Data Source=${Path}",
      "Extended Properties=`"${ExtendedProperties}`""
    ) -join ";"

    trap {& $close; break}
  }

  Process
  {  
    $DataSet.Tables.Clear()

    $Cmd.CommandText = $Query
    $Cmd.Connection  = $Connection 

    $DataAdapter.SelectCommand = $Cmd 
    $DataAdapter.Fill($DataSet) | Out-Null

    $DataSet.Tables[0]

    trap {& $close; break}
  }

  End {& $close}
}

# 出力するファイルを指定
$OUtFile = "JoinUserDate.csv"

# SQLを記述
# left outer joinで結合
$JoinCsv = @(
  "select UserOU.SamAccountName, DisplayName, DistinguishedName, MemberOf1, MemberOf2, MemberOf3 from UserOU.csv UserOU 
　　left outer join Member.csv Member on UserOU.SamAccountName = Member.SamAccountName"
) | 
# function 呼び出し
ForEach-Object{$_ | Join-Csv | 
# CSV出力する項目を指定
Select-Object SamAccountName, DisplayName, DistinguishedName, MemberOf1, MemberOf2, MemberOf
}

# CSV出力
$JoinCsv | Export-csv $OUtFile -Encoding Default -NoTypeInformation

実行するためのバッチファイル

64bitのWindows環境で↑のPowerShellを実行すると以下のようなエラーが表示されてしまう。

'Microsoft.Jet.OLEDB.4.0' プロバイダーはローカルのコンピューターに登録されていません。

'Microsoft.Jet.OLEDB.4.0'は、32bitバージョンはあるけれども、64ビbitバージョンが存在しないようなので、PowerShell(x86)を使用してスクリプトを実行するようにする。

Start-JionCsv.ps1

@echo off
if "%PROCESSOR_ARCHITECTURE%" NEQ "x86" (
    C:\Windows\SysWOW64\cmd.exe /C %0
    exit
)
powershell -NoProfile -ExecutionPolicy Unrestricted .\Join-Csv.ps1

PowerShell:CSVをSQLで結合してみる

CSVデータ

完成したスクリプト

実行するためのバッチファイル

参考

Discussion