PowerShell で CSV に SQL（Select文）を投げつける

q コマンドをインストール出来ないネットから隔絶された PC で、CSV ファイルに SQL を投げたかったので振休使って調べました。q コマンドについては下記参照。

• CSV形式のデータをSQLを使って解析する
• csvや標準入力をsqlで取得できるqコマンド

実装はCodeの節参照。

使ってみる

データ

文字コードはUTF-8

No,Ａ,Ｉ,Ｕ
1,あ,い,う
2,か,き,く
3,さ,し,す

No,Ｅ,Ｏ
1,え,お
3,せ,そ

投げつける Select 文

@(
  "select * from R.csv",
  "select * from S.csv",
  "select R.No, Ａ, Ｉ, Ｕ, Ｅ, Ｏ from R.csv R inner join S.csv S on R.No = S.No"
) | %{$_ | Select-Csv | Format-Table}

結果

No Ａ Ｉ Ｕ
--- - - -
  1 あ い う
  2 か き く
  3 さ し す

No Ｅ Ｏ
--- - -
  1 え お
  3 せ そ

No Ａ Ｉ Ｕ Ｅ Ｏ
-- - - - - -
 1 あ い う え お
 3 さ し す せ そ

参考にしたサイトとか

• powershell からのSQLserver接続

  • 今回の実装は、ここのコマンドの'OleDB mode'をCSVに特化させたもの。

• Textfile connection strings

  • CSVファイルなので、ConnectionString は Textfile 用になる。

• Microsoft.Jet.OLEDB.4.0 Converting Characters

  • 文字コードの設定方法はここで見つけた。

• Schema.ini File (Text File Driver)

  • 細かい設定は ini ファイルを作らないとできないらしい。
  • 区切り文字が ini ファイルでしか設定できないのがキツイ。

Code

function Select-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 = 'UTF-8',

    [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}
}