- 商品の価格をExcelに出力
- 得意先の一覧をExcelに出力
とかよく言われます。
この価格表一覧のとか、僕の得意先だけとか言われるので、
キーで指定して取得してこなければなりません。
それにExcel持ってない場合もある。
キーの一覧とSQLを指定したらDBのデータを取得してくれるPowerShellのCmdlet(Function)が欲しいな…。
という理由で作ってみました。
Get-OdbcDataFromPKs
必要となる変数
- SQL文 (パラメーター化クエリー)
- キーリスト
- OdbcのConnectionString
それらの変数を食べてプライマリキーリストから一つずつTableに格納。
結果をTableを出力。
CSVに吐くような作業はExport-Csvがやるので実装しない。
基本設計を元に色々思案していくと下記のようなCmdletが出来上がった。
Get-OdbcDataFromPKs.ps1
function Get-OdbcDataFromPKs{
<#
.Synopsis
パラメーター化クエリーと主キーリストから該当データを取得
Get the Data form Parameternized Query and KeyList
.DESCRIPTION
パラメーター化クエリーと主キーリストから該当データを取得する。
ODBCのDSN名やConnectionStringを指定して他のDatabaseでも使えます。
ODBC専用
.EXAMPLE
Get-OdbcDataFromPKs -q "SELECT id,name,price FROM items WHERE id=?" -IDs 0,1,2 | Format-Table
id name price
-- ---- -----
0 apple 100
1 orange 80
2 grape 120
# Get Data form IDs. IDsから主キーで取得
.EXAMPLE
Get-OdbcDataFromPKs -q "SELECT * FROM items WHERE id=?" -IDs 0,1,2 | Export-Csv -Path items.csv -Encoding UTF8 -NoTypeInformation
# 取得したデータをCsvファイルに出力
PS1 > Get-Content .\item.csv
"id","name","price"
"0","apple","100"
"1","orange","80"
"2","grape","120"
# check exported csv file. 出力したCsvファイルの確認
.EXAMPLE
Get-OdbcDataFromPKs -connectionString "DSN=stationer" -q "SELECT * FROM items WHERE id=?" -IDs 0,1,2
id name price
-- ---- -----
0 pencil 100
1 eraser 80
2 pencase 120
# 既定以外のデータベースでSELECT文を実行
.NOTES
System.Data.Odbc ODBC
System.Data.OleDb OleDb
System.Data.SqlClient SQL Server
使う.Netのオブジェクトによって、
OleDb用やSqlClient用のCmdletも作れます。
Author: Yusuke Arakawa
Created: 2016/8/23
#>
[CmdletBinding()]
param(
# ConnectionStrings 接続文字列
# 既存DBのConnectionString
[string]$connectionString="DSN=MyDB",
# Parameterized Queries パラメータ化クエリ
[string]$q,
# KeyList 読込んだキー一覧
[string[]]$IDs
)
Begin{
$Con = New-Object System.Data.Odbc.OdbcConnection($connectionString)
# コマンドオブジェクト作成
$Cmd=New-Object System.Data.Odbc.OdbcCommand
$Cmd.Connection=$Con
$Cmd.CommandText=$q
}
Process{
# DataAdapter
$da=New-Object System.Data.Odbc.OdbcDataAdapter
$da.SelectCommand=$Cmd
$Cmd.Connection=$Con.Open()
# table初期化
$table=$NULL
ForEach($id in $IDs)
{
$param=New-Object System.Data.Odbc.OdbcParameter("@id",[System.Data.SqlDbType]::Int);
$param.Value=[Int]$id
# OdbcParameterは戻り値を返すが不要なのでnullに封じ込める
$null=$Cmd.Parameters.Add($param)
$Cmd.Connection=$Con
# DataAdapter
$da=New-Object System.Data.Odbc.OdbcDataAdapter
$da.SelectCommand=$Cmd
# DataSetに格納
$DataSet=New-Object System.Data.DataSet
$nRecs=$da.Fill($DataSet)
$nRecs | Out-Null
# tableに1行ずつ格納
if($DataSet.Tables[0].Rows.Count -eq 0){
#何もしない
}else{
$table += $DataSet.Tables[0]
}
$Cmd.Parameters.Clear()
}
# Data出力
$table
}
End{
# Connection Close
$Con.Close()
}
}
使い方
Exampleに書かれているのは本当に簡単な使い方なんだけど、
ちょっと応用編を書いてみようと思う。
営業から「Excelで打ってきた商品cd一覧から商品名と価格を付けて欲しい。」
なんて言われたとしよう。
まずは、Excelをcsvにして保存する。
Get-Content(cat)で見てみよう。
商品cd一覧
PS > cat ItemcdList.csv
"id"
"0"
"1"
"2"
商品cd一覧をIDsで読込めるようなリストに変換する。
List化
PS > $itemids=Import-Csv -Path ItemcdList.csv
PS > $IDs=ForEach($id in $itemids){$id.id}
PS > cat $IDs
0
1
2
作成したキーリストとパラメーター化クエリーをGet-OdbcDataFromPKsに読ませる。
Get-OdbcDataFromPKsを実行
# Get-OdbcDataFromPKs.ps1を読み込んでCmdletを実行できるようにする
PS > . .\Get-OdbcDataFormPKs.ps1
# Get-OdbcDataFromPKsを実行
PS > $table = Get-OdbcDataFromPKs -IDs $IDs -q "SELECT id,name,price FROM items WHERE id=?"
PS > $table
id name price
-- ---- ------
0 apple 100
1 orange 80
2 grape 100
後はCSVに出力
csv出力
PS > $table | Export-Csv -Path ItemList.csv -NoTypeInformation
# 中身を確認
PS > cat ItemList.csv
"id","name","price"
"0","apple","100"
"1","oragne","80"
"2","grape","100"
このItemList.csvを渡してあげればいい。
Excelのデータで欲しいのであればLibreOffice Calcで開いてxlsx形式等の好きな形式に保存することをお勧めする。
Excelで開かないのは悩ましい問題があってね。
Csvを開くならExcelではなくCalcで