PowerShellでDBから最新のデータをCSVで取得

はじめに

PowerShellはデータベースからデータを取得するようなこともできます。
CSVとしてデータを吐き出して他のシステムが処理すること。
例えば、帳票印字のシステムがそのCSVを取得して印字するとかよくあります。

なぜ、Powershell?

Windowsであれば何処でも実行できてテキストエディタで修正可能。
.NETのライブラリーも使える。
つまり、非常に機動力が高くて強力なことができます。
こういう用途には持って来いかと思います。

常に新しいCSVデータを取得

帳票印字のシステムは一度印字したデータは必要ありません。
常に新しいデータを取得することになります。
なのでロジックはこんな感じになるでしょう。

1. CSVから一番新しい入力日付、時間、伝票noを取得
2. CSVの入力日付、時間、伝票noよりも新しいデータをDBから取得
3. CSVとして上書き

ソース

作ったのが下記のソース。
詳しい解説は後で書き足すとして、

1. Import-CSVを理解すること
2. そのImport-CSVの中から最大値を取ってくること
3. ODBCのParameterを理解すること
4. 日付入力から'00:00:00'の部分を除く等のテキスト処理

の4つです。

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"
$WarnngPreference = "Continue"
$VerbosePreference = "Continue"
$DebugPreference = "Continue"


#csvから該当データを読み込み変数にセット
#DBから該当データを検索してcsvに出力
#日付と時刻と売上伝票noを取得

##----csvからデータ取得 - start -

#スクリプトファイルのパスを取得
$path=Split-Path ( & { $myInvocation.ScriptName } ) -parent

#csvから検索対象を取得
$data=Import-Csv -Path $path'\sales.csv' -Delimiter "`t" | Measure-Object '入力日付','入力時間','売上伝票no' -Maximum
$input_date=$data.Item(0).Maximum
#入力日付から '00:00:00'の部分を除く
$input_date=$input_date.Substring(0,10)
$input_time=$data.Item(1).Maximum
#入力時間から'1200/01/01'を除く
$input_time=$input_time.Remove(0,11)
#入力時間から秒部分を除く
$input_time=$input_time.Substring(0,5)
$input_itemno=$data.Item(2).Maximum

##----csvからデータ取得 - end -

#ライブラリ読み込み
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data")

#DB接続
$connectionString="DSN=DSNNAME;uid=username;pwd=password;"
$odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionString)

#コマンドオブジェクト作成
$odbcCmd=New-Object System.Data.Odbc.OdbcCommand
$odbcCmd.Connection=$odbcCon

#SQL文
$sql="
SELECT
 * 
FROM 
売上d 
WHERE
入力日付>=?
AND
入力時間>?
AND
売上伝票no>?
ORDER BY
入力日付,
売上伝票no,
売上明細no
"

$odbcCmd.CommandText=$sql

$date=New-Object System.Data.Odbc.OdbcParameter("@date",[System.Data.SqlDbType]::Date);
$date.Value=$input_date
$odbcCmd.Parameters.Add($date)

$time=New-Object System.Data.Odbc.OdbcParameter("@time",[System.Data.SqlDbType]::DateTime);
$time.Value=$input_time
$odbcCmd.Parameters.Add($time)

$itemno=New-Object System.Data.Odbc.OdbcParameter("@itemno",[System.Data.SqlDbType]::Int);
$itemno.Value=$input_itemno
$odbcCmd.Parameters.Add($itemno)

#DataAdapter
$da=New-Object System.Data.Odbc.OdbcDataAdapter
$da.SelectCommand=$odbcCmd

#DataSetに格納
$DataSet=New-Object System.Data.DataSet
$nRecs=$da.Fill($DataSet)
$nRecs | Out-Null

# 時刻型のフォーマットを変更
# 9:00 > 09:00 に変更する。
# でないとSort時に9:00が最大値になってしまう。
# 実際にPowershellに書き込むときは 先頭の全角スペース"　"を取って入力して下さい。

　[System.Threading.Thread]::CurrentThread.CurrentCulture.DateTimeFormat.ShortTimePattern="HH:mm"
　[System.Threading.Thread]::CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern="HH:mm:ss"

#Datasetにデータがあるかの判別
if($Dataset.Tables[0].Rows.Count -eq 0){
  ##何もしない
}else{                         
  #DatasetからCSVに出力
  #-Delimiter tabは `t
  $table = $DataSet.Tables[0]
  $table | Export-Csv $path"\sales.csv" -Delimiter `t -encoding "UTF8" -notype
}

参考文献

CSVのデータから最大値を取得

[PowerShellからODBC接続してSQL実行してみました]
(http://qiita.com/kurukurupapa@github/items/acd64a02158911c46cb4)

[ODBC Parameter]
(http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter(v=vs.110).aspx)

Exporting SQLData to CSV by Paul Brice