Excel を読み取るコマンドレットを見つけたので SQL Server にデータを入れる PowerShell スクリプトを書いてみた。

こんなものを見つけました。

www.powershellgallery.com

Excel を読み取れる PowerShell のコマンドレットです。

思いつくことは 1 つですね?

そう、Excel でテストデータを作成し、データベースに Insert するスクリプトの作成です。

なので、試しに書いてみました。

Import-Excel コマンドレットの使い方

使い方は簡単で、こんな Excel シートに対しては、

f:id:neko3cs:20200913230900p:plain

以下の通り、ファイル名とシート名を指定して値を取得出来ます。

PS> Import-Excel -Path .\TestData.xlsx -WorksheetName "PriceOfFruits"

実行結果は以下の通りです。

f:id:neko3cs:20200913230642p:plain

ちなみに、シート名を指定せずに実行した場合は1番目のシートが取得対象シートになります。

こんな風にテーブルの開始セルが A1 から始まっていなくても、

f:id:neko3cs:20200913231004p:plain

以下のように、開始行と開始列を指定してあげれば取得出来ます。

PS> Import-Excel -Path .\TestData.xlsx -WorksheetName "AgeOfEmployee" -StartRow 4 -StartColumn 2

f:id:neko3cs:20200913234044p:plain

検証環境構築

以下のようなスキームのテーブルを雑に用意します。

f:id:neko3cs:20200913224851p:plain

よく使われそうな数字、文字列、時間、真偽値、あと、バイナリーも一応用意しました。

テストデータとなる Excel は以下の通りです。*1

f:id:neko3cs:20200913224932p:plain

データベースのテーブルに合わせて 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 関数とかでバイナリー値を読み取って渡したりする必要があると思うので少し工夫が必要です。

それ以外の型の場合は ' を付けちゃえばインサート出来そうです。

実行結果

スクリプトを実行してみた結果は以下の通りです。

f:id:neko3cs:20200913231733p:plain

スクリプトは正常に実行されています。

f:id:neko3cs:20200913230206p:plain

テーブルにもきちんと値が登録されました。

今回は 1 テーブルしか使いませんでしたが、シート名をテーブル物理名とかにしておけば foreach 回して複数テーブルにも対応出来そうですね。

また、sqlcmdSQL を発行するスクリプトを書きましたが、SQL ファイルを出力する PowerShell スクリプトを作ってもいいかもしれません。

汎用的なスクリプトが作れたら私の PowerShell スクリプト保管リポジトリ にでもおいておこうと思います。


2020.09.18 追記

汎用的なスクリプト一応作ったので貼っておきます。

varbinary に対応してないなど、完璧ではないですが、 identity_insert に対応させてあったりある程度使えると思います。

ご参考までにご活用ください。

github.com

*1:テストデータを Excel で作る場合はセルの型を文字列にしておくことを強く推奨します。時間とかが化けるので...。

dotnet tool install で 401 anauthorized エラーが出てインストール出来ないときの対処法

自作の暗号・復号化ツール CryptStr バージョンアップをしたのでツールを再ダウンロードしようとしたのですが、 以下の通り、401 でダウンロードに失敗してしまいました。

f:id:neko3cs:20200831105925j:plain

こんな時は以下のコマンドでキャッシュをクリアすると認証が通るようになることがあります。

PS> dotnet nuget locals http-cache --clear

このように認証が通ってインストール出来るようになります。

f:id:neko3cs:20200831105628j:plain

Git でユーザーを間違ってコミット→プッシュしてしまったときの対処法

私は Git 利用では個人用と仕事用の2つのユーザーを使い分けています。

このため、たまに仕事用のユーザーで個人のリポジトリにコミット→プッシュしてしまったりして、厄介です。

でも、Git にはあとからコミットしたユーザーを修正する方法が用意されています。

$ git filter-branch -f --env-filter \
    "GIT_AUTHOR_NAME='<user.name>'; \
     GIT_AUTHOR_EMAIL='<user.email>'; \
     GIT_COMMITTER_NAME='<user.name>'; \
     GIT_COMMITTER_EMAIL='<user.email>';" \
    <target commit | HEAD>
$ git push -f origin HEAD

上記のコマンドを実行すると指定したコミット、もしくは、すべてのコミットが指定したユーザー名、メールアドレスに書き換わります。

target commit にコミット ID を指定するとそのコミットが、HEAD を指定すると全コミットが対象になります。

書き換えた後はフォースプッシュしてリモートも書き換えます。

git filter-branch --commit-filter ' 
  if [ "$GIT_COMMITTER_NAME" = "<target user.email>" ];
    then
        GIT_AUTHOR_NAME="<user.name>";
        GIT_AUTHOR_EMAIL="<user.email>";
        GIT_COMMITTER_NAME="<user.name>";
        GIT_COMMITTER_EMAIL="<user.email>";
        git commit-tree "$@";
    else
        git commit-tree "$@";
    fi'  HEAD
$ git push -f origin HEAD

書き換えたい対象のユーザーを指定する場合は上記のコマンドを実行します。

データベースへの更新処理で Rollback/Commit の確認処理を自動化

SI事業では保守作業などでSQLを流してDB操作をすることがちょくちょくあります。(自社開発系は経験がないのでどうしてるか分かりません...)

今まで、ロールバックするように記述したスクリプトを発行し、正しく登録されることを確認してからコミットに書き直して登録という作業をしていました。

ですが、SQL Server には TRY-CATCH という構文が 2005 の時代からあるということを知ったのでメモしておこうと思います。

以下のように記載しておけば、コミットに書き直すなんて作業しなくなります。

begin try

    begin transaction

    -- 何かしらの更新処理

    commit transaction

end try
begin catch

    -- 以下の select 文でキャッチしたエラーの詳細を確認する
    select
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
    
    rollback transaction

    -- sqlcmd などのツールにエラーを伝播させるための throw
    -- SQL Server Management Studio で発行する場合は不要
    throw

end catch

他リポジトリのファイルをコミットも一緒に移行する方法

git を使っているとファイルを履歴ごと別のリポジトリに移行したい場合が発生すると思います。

例えば過去の構成管理ルールがあいまいでカオスになってしまったので一から作り直したいとか。

そんなリポジトリを移行したくなった時の手順をメモしておきます。

## 移行元リポジトリ格納フォルダ作成
$ mkdir .\<移行元リポジトリ格納フォルダ>
$ touch .\<移行元リポジトリ格納フォルダ>\.gitkeep
$ git add -A
$ git commit -m "取込用フォルダ作成"

## 移行元リポジトリをリモートリポジトリとして追加
$ git remote add <移行元リポジトリ名> <移行元リポジトリURL>
$ git fetch <移行元リポジトリ名>

## 移行元リポジトリを取り込み
$ git merge --allow-unrelated-histories -X \
subtree=<移行元リポジトリ格納フォルダ> <移行元リポジトリ名>/master

## 移行元のリモート登録を削除
$ git remote rm <移行元リポジトリ名>