LoginSignup
9
12

More than 5 years have passed since last update.

@minr(minr)

PowerShell with SQLite3 サンプル集

Last updated at Posted at 2015-11-23

自分用のメモとして。
PowerShellよりSQLite3を呼び出す関数を作成し、PowerShellを機能強化してみる。
内容は随時追加予定。

事前準備

１．SQLite3のインストール
※詳細は「SQLite 3.8.7.1のダウンロードとインストール」を参考
　ちなみに本記事では、SQLite3は「version 3.9.2」を使用

２．sqlite3.exeの格納フォルダを環境変数PATHに登録

ヘルパー関数

【InvokeSQLite3Script関数（自作関数）】
・SQLite3スクリプトを実行する
・実行はファイル経由（コマンド直接実行でINSERTすると文字化けした為）
・後述のサンプルで使う為、宣言しておく必要がある

関数定義
# string -> string
filter InvokeSQLite3Script($DatabasePath = ''){
  $readfile   = [System.IO.Path]::GetTempFileName()
  $outputfile = [System.IO.Path]::GetTempFileName()
  ;[System.IO.File]::WriteAllText($readfile,$_) #UTF-8

@"
.mode csv
.header on
.output $($outputfile.replace('\','/'))
.read $($readfile.replace('\','/'))
"@ | sqlite3 $DatabasePath

  ;[System.IO.File]::ReadAllText($outputfile) #UTF-8
  del $readfile
  del $outputfile
}
使用例
# 例１：test.dbにて実行
$testdata = 'temp\test.db'
"create table test(id,name);"           | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(1,'太郎');"    | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(2,'花子');"    | InvokeSQLite3Script $testdata | Out-Null
"insert into test values(10,'ジョン');" | InvokeSQLite3Script $testdata | Out-Null
"select * from test where id < 10;"     | InvokeSQLite3Script $testdata

# -> id,name
#    1,"太郎"
#    2,"花子"

# 例２：オンメモリで実行
@"
create table test(id,name);
insert into test values(1,'太郎さん');
insert into test values(2,'花子さん');
insert into test values(10,'ジョンさん');
select * from test where id < 10;
"@ | InvokeSQLite3Script

# -> id,name
#    1,"太郎さん"
#    2,"花子さん"

【SplitLine関数（自作関数）】
・行を分割し配列で返す
・改行コードがCR+LF(Windows)とLF(Linux)の両方の場合を考慮
・Firstスイッチ：1行目とそれ以降で分ける
・他のスイッチは、今後、必要に応じて追加する予定
・後述のサンプルで使う為、宣言しておく必要がある

関数定義
# string -> string,string
filter SplitLine{
  param(
    [switch]$First
  )

  if($First.IsPresent){
    $_.Substring(0,$_.IndexOfAny("`r`n")),$_.Substring($_.IndexOf("`n") + 1)
  }
}
使用例
$head,$tail = [System.IO.File]::ReadAllText((Resolve-Path filelist.csv)) | SplitLine -First

$head
# -> "Name","LastWriteTime","Extension"

$tail
# -> "cmd","2015/11/16 0:21:02",""
#    "html","2015/11/08 8:57:54",""
#    "php","2015/11/14 21:32:57",""
#    "powershell","2015/11/23 14:52:29",""
#    "svn","2015/11/08 12:27:15",""
#    "vba","2015/11/18 22:51:29",""
#    "wsh","2015/11/17 22:20:53",""
#    "data_win.csv","2015/11/23 1:26:15",".csv"
#    "filelist.csv","2015/11/23 17:53:32",".csv"
#    "utf8text.txt","2015/11/23 13:47:40",".txt"
テストデータ（filelist.csv）
"Name","LastWriteTime","Extension"
"cmd","2015/11/16 0:21:02",""
"html","2015/11/08 8:57:54",""
"php","2015/11/14 21:32:57",""
"powershell","2015/11/23 14:52:29",""
"svn","2015/11/08 12:27:15",""
"vba","2015/11/18 22:51:29",""
"wsh","2015/11/17 22:20:53",""
"data_win.csv","2015/11/23 1:26:15",".csv"
"filelist.csv","2015/11/23 17:53:32",".csv"
"utf8text.txt","2015/11/23 13:47:40",".txt"

【MeasureCsv関数（自作関数）】
・CSV形式の文字列から、行数や列数の情報を取得する
・「"」（ダブルクオーテーション）で囲まれている「,」（カンマ）や改行コード等も考慮
・列数は1行目の列数を取得（処理速度を優先する為）
・後述のサンプルで使う為、宣言しておく必要がある

関数定義
# string -> object
filter MeasureCsv{
  $rows = ($_.Trim() -replace '"(([^"]|"")*)"','"X"') -split "`n"
  @{
    RowsCount    = $rows.Length;
    ColumnsCount = ($rows[0] -split ',').Length;
  }
}
使用例
$testdata = 'data\KEN_ALL.CSV'
$encoding = [System.Text.Encoding]::GetEncoding("shift_jis")
;[System.IO.File]::ReadAllText((Resolve-Path $testdata),$encoding) | MeasureCsv
# -> Name                           Value
#    ----                           -----
#    ColumnsCount                   15
#    RowsCount                      123864

$testdata = 'data\KEN_ALL_dummy.CSV'
$encoding = [System.Text.Encoding]::GetEncoding("shift_jis")
;[System.IO.File]::ReadAllText((Resolve-Path $testdata),$encoding) | MeasureCsv
# -> Name                           Value
#    ----                           -----
#    ColumnsCount                   15
#    RowsCount                      4

テストデータその１
（日本郵便株式会社のサイトより入手）

KEN_ALL.CSV
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0

～　中略　～

47382,"90718","9071800","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁｮｳ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","沖縄県","八重山郡与那国町","以下に掲載がない場合",0,0,0,0,0,0
47382,"90718","9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁｮｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0

テストデータその２
（その１より抜粋し、「"」（ダブルクオーテーション）内に「,」（カンマ）や改行コード等を混ぜてみた）

KEN_ALL_dummy.CSV
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0

ファイル操作系

【ReadCsv関数（自作関数）】
ReadCsv関数は少し仕様が濃いので、詳しくは後日、別記事にて解説予定。
今回は一部のみ解説。

・CSVファイルをSQLite3データベース（DatabasePathで指定）に取り込む
・DatabasePath未指定の場合は、オンメモリ実行
※CSV取り込み後の実行クエリをQuery引数にて指定することで結果取得可能

・CSVファイルは複数指定可能
・テーブル名は自動取得（ファイル名の「.」（ドット）より前部分）
・列名については以下のように取得／生成される
　・ヘッダありモード（デフォルト）→ CSVファイルの1行目より取得
　・ヘッダなしモード → 自動採番（_1,_2 ... ）

・CSV取り込み後の実行クエリをQuery引数にて複数指定可能
・CSVファイルの文字コードを指定可能（ファイル毎に指定可能）
　省略時はUTF-8が指定される

関数定義
# string[] -> string
function ReadCsv{
  param(
    $DatabasePath = '', #単一指定
    $Query        = '', #複数指定可能
    $Encoding     = '', #複数指定可能
    [switch]$NoHeader
  )
  $defaultEncoding = 'utf-8'
  $tempFileList    = @()
  $tableNameList   = @()
  $createSql       = ''
  $importCmd       = ''

  # SQL文生成
  $csv_i = 0
  $input | foreach{
    # CSVファイルの文字コードの指定
    $objEncoding = `
      switch($Encoding.GetType().Name){
        # 個別指定
        'Object[]' {
          if($csv_i -lt $Encoding.Length){
            [System.Text.Encoding]::GetEncoding($Encoding[$csv_i])
          } else {
            [System.Text.Encoding]::GetEncoding($defaultEncoding)
          }

        # 一括指定
        } 'String' {
          if($Encoding -eq ''){
            [System.Text.Encoding]::GetEncoding($defaultEncoding)
          } else {
            [System.Text.Encoding]::GetEncoding($Encoding)
          }
        }
      }

    # .NetクラスのファイルI/Oではフルパスの指定が必要
    $absolutePath = Resolve-Path $_

    $tableName    = (Split-Path $_ -Leaf).split('.')[0]

    # ヘッダなしモード
    if($NoHeader.IsPresent){
      # CSVファイルの読み込み
      $data     = [System.IO.File]::ReadAllText($absolutePath,$objEncoding)

      # 列名の自動採番
      $col_n    = ($data | MeasureCsv).ColumnsCount
      $colNames = (1..$col_n | foreach{'_' + $_}) -join ','

    # ヘッダありモード
    } else {
      # CSVファイルの内容を列名とデータに分割
      $colNames,$data = [System.IO.File]::ReadAllText($absolutePath,$objEncoding) | SplitLine -First
    }

    # インポートファイル生成
    $importfile    = [System.IO.Path]::GetTempFileName()
    $tempFileList += $importfile
    [System.IO.File]::WriteAllText($importFile,$data) #UTF-8

    # CREATE文生成
    # テーブル名が被った場合は先勝ち（初めのCSVの定義を優先する）
    if(!$tableNameList.Contains($tableName)){
      $tableNameList += $tableName
      $createSql     += "CREATE TABLE $tableName ($($colNames.Replace('"','')));" + "`n"
    }

    # importコマンド生成
    $importCmd += ".import $($importFile.replace('\','/')) $tableName" + "`n"

    $csv_i++
  }

  # SQLite3の実行
@"
$createSql
$importCmd
$($Query -join "`n")
"@ | InvokeSQLite3Script $DatabasePath

  # 一時ファイルの削除
  $tempFileList | foreach{del $_}
}
使用例
$testdata1_1 = 'data\KEN_ALL_dummy.CSV'
$testdata1_2 = 'data\KEN_ALL_dummy_euc.CSV'
$testdata2 = 'temp\test.db'

#実行
$result = & {
  $testdata1_1,$testdata1_2 | ReadCsv -DatabasePath $testdata2 `
                              -NoHeader `
                              -Encoding @('shift_jis','euc-jp') `
                              -Query @'
select 
  euc._1, 
  euc._3, 
  euc._5 as euc_5, 
  euc._7 as euc_7,
  sjis._5 as sjis_5, 
  sjis._7 as sjis_7
from KEN_ALL_dummy_euc as euc,
     KEN_ALL_dummy as sjis
where euc._1 = sjis._1
  and euc._3 = sjis._3
;
'@
}

#確認
$result | ConvertFrom-Csv | Format-List
# -> _1     : 01101
#    _3     : 0600000
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道
#    
#    _1     : DUMMY1
#    _3     : 0600000
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳ
#             ｵｳｸ
#    euc_7  : 北海道"
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳ
#             ｵｳｸ
#    sjis_7 : 北海道"
#    
#    _1     : DUMMY2
#    _3     : 0600000
#    euc_5  : ｻｯﾎ
#             ﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎ
#             ﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道
#    
#    _1     : 01101
#    _3     : 0600031
#    euc_5  : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    euc_7  : 北海道
#    sjis_5 : ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ
#    sjis_7 : 北海道

テストデータ

KEN_ALL_dummy.CSV（文字コード：Shift_JIS）
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0
KEN_ALL_dummy_euc.CSV（文字コード：EUC_JP）
01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY1,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳ
ｵｳｸ","ｲｶﾆｹｲｻｲｶﾞ
ﾅｲﾊﾞｱｲ","北海道""","札幌市,中央区","以下に掲載がない場合",0,0,0,0,0,0
DUMMY2,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎ
ﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"060  ","0600031","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾋｶﾞｼ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0
9
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
12