こんなものを見つけました。
Excel を読み取れる PowerShell のコマンドレットです。
思いつくことは 1 つですね？
そう、Excel でテストデータを作成し、データベースに Insert するスクリプトの作成です。
なので、試しに書いてみました。
Import-Excel コマンドレットの使い方
使い方は簡単で、こんな Excel シートに対しては、
以下の通り、ファイル名とシート名を指定して値を取得出来ます。
PS> Import-Excel -Path .\TestData.xlsx -WorksheetName "PriceOfFruits"
実行結果は以下の通りです。
ちなみに、シート名を指定せずに実行した場合は1番目のシートが取得対象シートになります。
こんな風にテーブルの開始セルが A1 から始まっていなくても、
以下のように、開始行と開始列を指定してあげれば取得出来ます。
PS> Import-Excel -Path .\TestData.xlsx -WorksheetName "AgeOfEmployee" -StartRow 4 -StartColumn 2
検証環境構築
以下のようなスキームのテーブルを雑に用意します。
よく使われそうな数字、文字列、時間、真偽値、あと、バイナリーも一応用意しました。
データベースのテーブルに合わせて Excel にテーブルを用意します。
今回は面倒くさいのでバイナリーには NULL を入れます。
スクリプトの実装
Excel からデータを読み取るスクリプトは以下の感じで実装しました。
#!pwsh param( [string]$Path, [string]$WorkSheetName ) $SampleTable = Import-Excel $Path ` -WorksheetName $WorkSheetName foreach ($Record in $SampleTable) { sqlcmd ` -S localhost ` -E ` -Q "insert into [TestDatabase].[dbo].[SampleTable] values ('$($Record.IntValue)','$($Record.StringValue)','$($Record.DateTimeValue)', '$($Record.BooleanValue)', $($Record.BinaryValue));" }
動作確認程度なので結構雑に書いてます。
重要なのは ' の有無だと思います。
NULL の場合は ' を外さないとエラーか nvarchar とかの場合は文字列として入ってしまうので if 文とかで分岐させる必要がありますね。
varbinary とかも OPENROWSET 関数とかでバイナリー値を読み取って渡したりする必要があると思うので少し工夫が必要です。
それ以外の型の場合は ' を付けちゃえばインサート出来そうです。
実行結果
スクリプトを実行してみた結果は以下の通りです。
スクリプトは正常に実行されています。
テーブルにもきちんと値が登録されました。
今回は 1 テーブルしか使いませんでしたが、シート名をテーブル物理名とかにしておけば foreach 回して複数テーブルにも対応出来そうですね。
また、sqlcmd で SQL を発行するスクリプトを書きましたが、SQL ファイルを出力する PowerShell スクリプトを作ってもいいかもしれません。
汎用的なスクリプトが作れたら私の PowerShell スクリプト保管リポジトリ にでもおいておこうと思います。
2020.09.18 追記
汎用的なスクリプト一応作ったので貼っておきます。
varbinary に対応してないなど、完璧ではないですが、 identity_insert に対応させてあったりある程度使えると思います。
ご参考までにご活用ください。
