【PowerShell入門】DB連携用バッチ処理で活用するための基礎知識まとめ

今回の記事では、Windowsのコマンドラインインターフェイス(CLI)であり、標準搭載のスクリプト言語であるPowerShellを初めてさわる人に向けて、PowerShellの特徴、変数やコメントなどの言語仕様における基礎的な部分を解説していきます。

また、実務でよく利用されるPowerShellの用途である、「業務システムなどのデータベースと連携するバッチ処理として活用する場合に知っておいてほしい知識や実装例」も紹介していきます。

私がPowerShellでバッチ処理を作る場合はこの記事でまとめた実装方法を使っています。
良ければ参考にしてください。

 
 

PowerShellで作るバッチ処理の活用例

PowerShellでは、Windowsに対する様々な操作が行えるとともに、現代のモダンなプログラミング言語と同様に、オブジェクト指向なプログラムの実装が可能です。

また、C#やVB.NETなどと同様に、.NET Framework(又は.NET Core)を基盤としていることで、.NETのライブラリを呼び出して活用することが可能です。
よって、あらゆる処理の自動化に利用することができます。

企業で使用するバッチ処理では、以下のような処理を実装するケースも多いかと思います。

などなど。

これらの処理も、PowerShellであれば容易に実装することが可能です。

今回の記事では、よく利用される用途である「データベースを参照してCSV形式のファイルを出力する」を場合を想定して、実際にサンプルコードを用いながら、バッチ処理を実装するうえで知っておいてほしい基礎的な知識を解説していていきます。

 

PowerShellでプログラミングをするための基礎知識

当項では、PowerShellを使ってバッチ処理を作成する場合における、主にプログラミング関連の基礎知識や知っておいた方がよいポイントと、実際のサンプルコードを紹介していきます。

 

.NET Frameworkと.NET Core

PowerShellでは、Microsoftが開発したアプリケーションのプラットフォームである.NETのテクノロジーの上で動作します。
現在の.NETは2種類あり「.NET Framework」と「.NET Core」があります。

.NET FrameworkはWindows自体に組み込まれており、Windows自体もこの.NET Frameworkと連携しながら動作しています。
.NET Frameworkはバージョン 4.8 が最終のバージョンであり、現在はセキュリティやバグ対応以外の開発は行われておりません。

.NET Framework - Wikipedia

ja.wikipedia.org

現在は.NET Frameworkの後継として.NET Coreが提供されており、.NET Coreは.NET Frameworkと異なりマルチプラットフォームに対応しております。
よって、Windows環境だけではなく、Linuxなど他のOS上でも動作します。
当記事の執筆時点での.NET Coreの最新バージョンは .NET 8 です。
※正確には名称も「.NET Core」から「.NET」に変更されていますが、当記事では旧称の「.NET Core」と呼称します。

.NET - Wikipedia

ja.wikipedia.org

尚、.NET CoreはWindows標準でインストールはされておらず、必要により自身でインストールをして環境を導入します。

今回の記事で紹介するPowerShellのバージョンが5.1までが.NET Frameworkを使用し、それ以降のPowerShellでは.NET Coreを使用します。

上記の理由により、Windows標準で利用できるのはPowerShell 5.1です。
以降のPowerShellとは当然実装されている機能にも相違があります。

PowerShellについてインターネットで情報を収集する際には、どのバージョンのPowerShellについて書いているのかを意識するようにしてください。

 

コマンドレット

PowerShellでは、PowerShell独自に用意されたコマンドがあり、それらを「コマンドレット」と呼びます。

「コマンドレット」に対するMicrosoftの解説は以下のリンク先にあります。

PowerShell コマンドとは何ですか。 - PowerShell

PowerShell のコマンドは、cmdlets と呼ばれます (「コマンドレット」と発音されます)

learn.microsoft.com

このリンク先では、「コマンドレットとは」について解説しております。
その一文を引用します。

コマンドレットとは
コマンドレットはネイティブの PowerShell コマンドであり、スタンドアロンの実行可能ファイルではありません。 コマンドレットは、必要に応じて読み込むことができる PowerShell モジュールにまとめられています。 コマンドレットは、任意のコンパイルされた .NET 言語または PowerShell スクリプト言語自体で記述できます。

この説明では、いまいち何のことだかよくわかりません。

よって、上記のリンク先を無視して私の言葉で説明しますが、「コマンドレット」とは、PowerShellが用意した専用の関数のようなもので、.NETのオブジェクトで戻り値を返します。

コマンドレットのコマンド名は長いものが多く、[動詞]-[名詞]の命名規則で作られています。

PowerShellのコマンドレットでは、OSの状態や設定の取得、CSVなど外部ファイルの読み込みや書き込み、オブジェクトの生成、パスの取得や各種操作など様々なものが用意されており、それをスクリプトの構文に組み込みながら処理を実装していきます。

簡単なコマンドレットの例としては以下です。

  # コマンドレットの一覧を取得します。
  Get-Command
  
  # OSのプロセスの一覧を取得します。
  Get-Process

コマンドレットを試しに実行するだけなら、Windowsのスタートメニュー内の「Windows PowerShell」を起動して、そこでコマンドレットを入力して実行できます。

 

ps1ファイル

PowerShellでスクリプトを作る場合は、空のテキストファイルを作成し、そのテキストファイルの拡張子を「.ps1」に変更します。

拡張子をps1にすることで、そのファイルはPowerShellのスクリプトファイルとしてWindowsから認識され、ps1ファイルを右クリックすると、「PowerShellで実行」というメニューが表示されるようになります。

尚、現在のWindowsの既定の文字コードはUTF-8であり、テキストファイルを新しく作成すると、既定の文字コードもUTF-8が使われます。
しかし、PowerShellではUTF-8のまま日本語を含むスクリプトを記述すると文字化けして正常に動作しません。

よって、ps1ファイルを日本語環境で作成する場合は、必ずShift-JISに変換して使用してください。

メモ帳で文字コードを変更する場合は、ウィンドウ上部の[ファイル]→[名前を付けて保存]を選択した後、ファイル名を指定する箇所の下部にある[文字コード]を「ANSI」に変更して保存してください。
※「ANSI」はMicrosoftが独自に拡張したShift-JIS(CP932)の名称です。

余談ですが、PowerShellでスクリプトを作っていると、構文や記述に誤りが見つけられず正常に動作するはずのコードでエラーになり、原因がわからずハマるときがたまにあります。

その場合はたいてい文字コードがShift-JISになっていないケースです。

PowerShellを使ったスクリプト作成に慣れていても、うっかりやってしまいがちなミスなので、文字コードには十分注意してください。

 

テキストエディタ

PowerShellを使ってスクリプトを書く場合、前述した「メモ帳」でもコードを記述できますが、それだと「入力補完機能」や「シンタックスハイライト」が使えないため非常に不便です。

よって、PowerShellを使ってスクリプトを作る場合は、一般的には以下の二通りの方法があります。
 

Windows PowerShell ISE

「Windows PowerShell ISE」とは、Windowsに標準でインストールされているPowerShell用の簡易的な「統合開発環境(IDE)」です。

ps1ファイルの読み込みやコードの記述ができて、入力補完機能があり、シンタックスハイライトも効きます。
作成したスクリプトの実行やデバッグをすることも可能です。

PowerShellでスクリプトを作成するにあたり、テキストエディタのインストールに制約がある場合はこちらを使用することなります。

ただし、この「Windows PowerShell ISE」は、後述する「Visual Studio Code」と比べると使い勝手は悪く、MicrosoftもこのISEの積極的な開発は終了しており、.NET CoreベースであるPowerShell 6.0以降のバージョンには対応していません。

よって、PowerShell ISEでスクリプトを書いても問題はないのですが、できれば後述する「Visual Studio Code」の利用をおススメします。
 

Visual Studio Code

「Visual Studio Code(以降VS Codeと呼称)」はMicrosoftが開発し、無償で提供されている多機能テキストエディタです。
様々なユーザーや団体が公開している拡張機能を検索してインストールすることで、より便利な機能拡張を行うことが可能です。

VS Codeでは、拡張子ps1形式のファイルを読み込むと、自動的にPowerShellのスクリプトとして扱ってくれます。
PowerShellの場合は、拡張機能を追加することなく入力補完機能やシンタックスハイライトが使えます。
また、スクリプトの実行やデバッグもVS Code内で行うことが可能です。

VS CodeはWindows標準ではインストールされていないため、VS Codeを使いたい場合は、Microsoftのダウンロードページからダウンロードして、対象の端末にインストールしてあげることが必要です。

https://azure.microsoft.com/ja-jp/products/visual-studio-code

azure.microsoft.com

テキストエディタのインストールについて制約が無ければ、多機能でPowerShellと相性も良いVS Codeをおススメします。

尚、当記事の以降の内容は、VS Codeを使用してスクリプトの作成する前提で解説していきます。

 

VS CodeでPowerShellのデバッグ方法

VS Codeを使用して、PowerShellので作成したスクリプトをデバッグする方法を紹介しておきます。

まずは、ps1ファイルをVS Codeで開きます。

画面上部のメニュー内の[実行]→[デバッグの開始]を選択すると、読み込まれているスクリプトが最初から最後まで実行されます。

実行時の各出力は画面下部[ターミナル]に表示されます。

行ごとに処理を1行ずつ実行する場合は、画面左側のブレイクポイントを設置できます。

ブレイクポイントを設置してから[デバッグの開始]をすると、ブレイクポイントの位置でプログラムの実行は中断するため、後は[実行]のなかにある[ステップオーバーする]や[ステップインする]を選択することで1行ずつプログラムを実行できます。

 

VS Codeで文字コードを変更する方法

VS Codeを使用して、現在開いているテキストファイルの文字コードを変換する方法を紹介しておきます。

前述したように、現在のWindowsの既定の文字コードはUTF-8であり、新しく作成したテキストファイルの拡張子をps1に変更しても、そのps1ファイルの文字コードはUTF-8として読み込まれます。

その場合、PowerShellでは日本語がすべて文字化けすることになるため、Windows環境では、日本語を含むPowerShellのスクリプトを作成する場合は、必ず次の操作で文字コードをShift-JISに変更してください。

新しく作成したps1ファイルをVS Codeで開き、画面下部に現在使用されている文字コードが表示されています。

新規に作成したファイルの場合、通常は「UTF-8」が表示されています。

こちらの表示をクリックすると、画面上部に「エンコード付きで再度開く」と「エンコード付きで保存」が表示されます。
通常は「エンコード付きで保存」を選択します。

エンコード可能な文字コード名の一覧が表示されるため、下にスライドし、「Japanese (Shift-JIS)」を選択します。
これで現在開いているps1ファイルはShift-JISとして扱われます。

 

変数

PowerShellでは、文字列の先頭に”$”を付けることで変数として扱われます。

また、PowerShellでは、型を指定を省略することも出来ますし、型を指定することもできます。
型の指定を省略した場合は変数に代入した値をもとに型が決まります。

  # 変数に文字列を代入します。
  $variable = "Hello, PowerShell"
  
  # 変数の値をコンソールに出力します。
  Write-Output $variable

変数に型を指定する場合は、以下のサンプルのように、変数宣言時の変数名の前に [型] を付与します。

  # 変数に型を指定します。
  [int]$intVariable = 10
  [string]$stringVariable = "Hello"
  [bool]$boolVariable = $true
  [array]$arrayVariable = @(1, 2, 3)

詳しくは以下のMicrosoftのLearmをご確認ください。

後、PowerShellの変数で注意が必要なのは、変数に代入した値は、PowerShellのコンソールが起動している限り値を保持し続けます。

また、例えばVS Codeでデバッグをする場合、スクリプトを実行し、その処理で変数に代入した値は、スクリプトの実行が完了しても保持しており、VS Codeを終了することなく、再度同じスクリプトをデバッグで実行すると、最初から変数に値が保持された状態で実行されます。

  # 変数に値を代入します。
  $variable = "テスト"
  # 変数の値を出力します。
  $variable

上記のスクリプトをデバッグで実行した後、変数へ値を代入する処理をコメントアウトしてみます。

  # 変数に値を代入します。
  # $variable = "テスト"
  # 変数の値を出力します。
  $variable

すると、値を代入していないはずの変数$variableで値が入っているのが確認できます。

よって、変数は必ず初期化してから使用するようにしてください。

尚、変数を初期化するには、初期値として必ず最初に値を代入するか、又は「Remove-Variable」コマンドレットを使います。
因みに、「Remove-Variable」コマンドレットでは、変数名を引数として渡しますが、その場合は”$”を付けずに変数名を指定します。

  # 変数に値を代入します。
  Remove-Variable variable

 

コメントとコメントアウト

PowerShellでは、コメントを意味する記号として # を使用します。

単一行のコメントであれば # ですが、スクリプトのコードを複数行に渡ってコメントアウトする場合は、先頭行に <# 、末尾に #> を記載します。

  # 単一行をコメントにするには # を使用します。
  
  <#
  複数行に
  渡ってコメント化するには
  不等号と# で囲みます。
  #>

 

オブジェクト

PowerShellではコマンドレットの戻り値はオブジェクトであるため、コマンドレットの戻り値を変数に代入することで、その変数もオブジェクトとして扱えます。

  # コマンドレットGet-Processの戻り値を変数に代入します。
  $process = Get-Process
  
  # プロパティの値を参照します。
  $process.Name

 

パイプライン(パイプ)

Linuxでは、コマンド同士を |(パイプ) で挟んで繋げることで、標準出力を別のコマンドに渡すことができますが、PowerShellでも同様に |(パイプ) が使用できます。

パイプでは、先頭のコマンドレットの結果を後ろのコマンドレットに渡します。
パイプを活用することで、複数のコマンドレットを組み合わせた複雑な処理を完結に記述することができます。

また、PowerShellでは「自動変数」と呼ばれる、自動で値が入る組み込み変数が用意されています。

パイプ処理では、幾つかある自動変数のなかでも、$_ (ダラー、アンダースコア) を組み合わせて使用するケースが多いです。

$_ は直近で実行したコマンドレットの出力がオブジェクトとして格納されています。

パイプでコマンドレット同士を繋いだ場合、前のコマンドレットの出力はこの変数に入っており、次のコマンドレットでは、この変数を引数に使用して処理をするといった使い方です。

PowerShellにおける自動変数の一覧及び$_の説明は以下を参照してください。

  # 実行中のサービスを表示します。
  Get-Service | Where-Object { $_.Status -eq "Running" }

尚、このサンプルでは、コマンドレット「Where-Object」を使用しています。
このコマンドレットは、引数で渡されたオブジェクトを検索します。
このコマンドレットの詳しい説明は以下を参照してください。

 

条件分岐と比較演算子

PowerShellでは、他のプログラミング言語と同様にIF文による条件分岐処理が実装できます。

尚、PowerShellでは、JavaやC#などの言語と同様に、条件式を()で囲み、trueの場合の処理と、falseの場合の処理を記述したブロックをそれぞれ{}で囲みます。

  # ifのみ
  if (条件) {
    # 条件が真の場合に実行されるコード
  }

  # if～else
  if (条件) {
    # 条件が真の場合に実行されるコード
  }
  else {
    # 条件が偽の場合に実行されるコード
  }

  # if～elsif～else
  if (条件1) {
    # 条件1が真の場合に実行されるコード
  }
  elseif (条件2) {
    # 条件2が真の場合に実行されるコード
  }
  else {
    # 条件が偽の場合に実行されるコード
  }

尚、if文における条件式でも使用する「比較演算子」ですが、PowerShellの場合は記号ではなく文字列を使用します。
以下は、PowerShell、Python、JavaScript、C#それぞれの比較演算子の一覧です。

また、PowerShellの条件分岐を実装する場合の簡単なサンプルコードは以下になります。

  # 数値を設定
  $number = 15

  # 条件分岐
  if ($number -gt 20) {
    Write-Output "この番号は20より大きい"
  }
  elseif ($number -gt 10) {
    Write-Output "この番号は10より大きく20以下"
  }
  else {
    Write-Output "この番号は10以下"
  }

 

関数の作成

PowerShellでは、他のプログラミング言語と同様に、関数を作成することができます。
関数を定義するには、戻り値の有無に関わらず Function を使用します。

簡単な関数の実装例は以下です。

  function fnc-test {
    param (
        [string]$a,  # 最初の数値
        [int]$b   # 二番目の数値
    )
    
    # 引数を結合
    $val = $a + $b.ToString();
    
    # 結果を返す
    return $val
  }

  # 関数の呼び出しと結果の表示
  $result = fnc-test "test" 10
  Write-Output $result

PowerShellの関数を定義するうえで注意すべき事項は以下です。

• 関数はスクリプトの先頭に定義しないと読み込まれない。
• 定義した関数に引数を渡す場合は引数同士をスペースで区切る。
• 引数が不要であれば、param() 部分は不要。
• 戻り値が不要であれば return は不要。

 

エラーハンドリング処理

PowerShellでは、他のプログラミング言語と同様に、Try-Catchでのエラーハンドリング処理が実装できます。
エラーが発生する可能性のあるコードを try{} で囲み、その後ろの catch{} でエラー発生時の処理を記述します。

基本的な構文は以下です。

  try {
    # エラーが発生する可能性のあるコード
  }
  catch {
    # エラーが発生したときの処理
  }

また、PowerShellでは、finally も使用できます。
finally は、エラーの発生有無に関わらず、必ず最後に実行される処理のブロックを記述します。

  try {
    # エラーが発生する可能性のあるコード
  }
  catch {
    # エラーが発生したときの処理
  }
  finally {
    # エラーの発生有無に関わらず最後に行う処理
  }

Try-Catchの実装例は以下です。

  try {
    # 存在しないファイルを読み込もうとする
    $content = Get-Content -Path "C:\path\file.txt"
    Write-Output "ファイルの内容: $content"
  }
  catch {
    # エラーが発生した場合の処理
    Write-Output "エラーが発生しました: $_"
  }

あと、テスト用などでわざとエラーを発生させたいケースもあります。
その場合は、Throw コマンドが使用できます。

  try {
    # 次のステップで強制的にエラーにします。
     throw
  }
  catch {
    # エラーが発生したときの処理
  }

throwの使い方については以下を参照してください。

 

PowerShellでデータベースの参照やCSV出力を実装する場合のサンプルコードと解説

前項では、PowerShellについて経験が浅い人がPowerShellを理解するうえで必要になる入門的な知識を紹介しましたが、当項では、PowerShellでデータベースを参照する場合やCSVを出力する場合に知っておく必要がある知識についてサンプルコードを交えて紹介していきます。

 

データベースへの接続

PowerShellでデータベースと接続する処理の実装例とその解説をしていきます。
尚、当サンプルではSQL Serverとの接続する想定のコードです。

他のRDBMSの場合は、接続文字列部分を適切に書き換えて使用してください。

当サンプルでは、データベースとの接続するところまでを実装しています。

  # データベースとの接続情報を定義します。
  $serverName = "DBのIP又はホスト名"
  $databaseName = "データベース名"
  $username = "DBユーザー"
  $password = "ユーザーのパスワード"

  # 接続文字列を作成します。
  $connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;"

  # データベースへの接続用のオブジェクトを作成します。
  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString

  # 接続を開きます。
  $connection.Open()

  # 接続を閉じる
  $connection.Close()

上記コードでは、コマンドレット「New-Object」を使用してConnection用オブジェクトを作成しています。
New-Objectでは、.NET Framework 又はCOMオブジェクトのインスタンスを作成できます。

上記のコードでは、.NET FrameworkのSQL Server接続用のクラスをNew-Objectに渡してConnectionインスタンスを作成しています。

OracleやMySQLなどSQL Server以外のデータベースと接続する場合は、ODP.NETやMySQL .NET Connectorなどのデータベース用ドライバをインストールしておく必要があります。
また、スクリプトの冒頭で、アセンブリを名前やDLLを指定して読み込んでおくことが必要です。

  # Oracleの場合の例
  Add-Type -AssemblyName System.Data.OracleClient
  # 又はDLLを直接指定
  Add-Type -Path "C:\path\Oracle.ManagedDataAccess.dll"
  
  $connectionString = "Oracleの接続文字列"
  
  $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

 

SQL文の埋め込みとヒア文字列

当項では、PowerShellでSQL文を文字列変数に格納する場合の方法と、知っておくと便利なPowerShellの「ヒア文字列(ヒアドキュメント)」についても解説します。

まずはPowerShellでSQLを生成する場合は、他のプログラミング言語と同様に、文字列として変数に格納します。
1行で表現できる簡単なSQLであれば、以下のような記述で構いません。

  # 変数queryにSQL文を格納
  $query = "SELECT * FROM table_name"

ただ、これだと、複数行に渡る長いSQLを変数に収める場合は手間です。
例えば以下のような書き方になります。

  # 変数queryに複数行のSQL文を格納
  $query = "SELECT "
  $query += "* "
  $query += "FROM "
  $query += "table_name"

これでも大きな支障はないですが、各行を変数で結合する記述はスマートではないですし、変数内のSQL文のインデントが無くなるのも嫌です。

このようなケースの場合、PowerShellでは「ヒア文字列(ヒアドキュメント)」を使います。
「ヒア文字列」は改行やスペースなどのインデントも含んだままの状態を文字列としてソースコードに埋め込むことができる方法です。

PowerShellのヒア文字列は二種類あり、文字列に変数を埋め込める方法と埋め込めない方法があります。

PowerShellのヒア文字列を使うには、格納する文字列の先頭と末尾に、@” “@ で囲みます。
また、先頭と末尾にはそれぞれ改行が必要です。

@で囲む際に、ダブルクォーテーションを使った場合は文字列内に変数を埋め込むことができ、シングルクォーテーションを使った場合は変数を埋め込むことはできません。

具体的には以下のように使用します。

  # ヒア文字列を使ったSQL埋め込み※変数無し
  $query = @'
  SELECT
  	*
  FROM
  	table_name
  '@
  
  # ヒア文字列を使ったSQL埋め込み※変数有り
  $query = @"
  SELECT
  	*
  FROM
  	table_name
  WHERE
  	ID = $ID
  "@

ヒア文字列であれば、記述したインデントのまま変数に格納されるため、コード内で生成されたSQL文をデバッグ時に変数から取得して確認する場合なども便利です。

尚、生成するSQL文のWHERE句で動的な条件を指定するケースは多いため、変数の埋め込みに対応した種類(ダブルクォーテーション)のヒア文字列を常に使用しても良いかと思います。

PowerShellのヒア文字列については以下のリンク先にて詳細をご確認ください。

 

DataSetとDataTable

当項では、PowerShellでデータベースを扱う場合によく使用することになる「DataSet」と「DataTable」についての解説していきます。

「DataTable」とはデータを列と行の形式で扱えるデータ構造のオブジェクトであり、コード内で生成して読み書きができる一時テーブルのようなものです。
PowerShellでは.NETの「System.Data.DataTable」クラスを使用できます。

「DataSet」とは、DataTableを複数格納することができるオブジェクトです。
PowerShellでは.NETの「System.Data.DataSet」クラスを使用できます。

一般的なデータベースに例えると、DataSetがデータベースやスキーマなどになり、DataTableが個々のテーブルです。
また、DataSetオブジェクトを作成して、そこからDataTableを作成することも出来ますし、DataSetオブジェクトは作らずにDataTableオブジェクトを直接作成することもできます。

 

DataTableの作成から行追加までの解説

当項では、PowerShellでDataTableを作成し、列を定義して行を追加する一連のコードを解説します。

まずはNew-ObjectコマンドレットでSystem.Data.DataTableクラスにアクセスし、DataTableオブジェクトを作成します。

  # DataTableオブジェクトを作成
  $dataTable = New-Object System.Data.DataTable

データテーブルに列を定義します。
列を定義するにはDataTableオブジェクトのColumns.Addメソッドを使用します。

  # 列を追加
  $dataTable.Columns.Add("ID", [int])
  $dataTable.Columns.Add("Name", [string])
  $dataTable.Columns.Add("Age", [int])

データテーブルに対して1件ずつ行を追加する場合は、NewRowオブジェクトを作成し、そこに値を設定して、Rows.Addメソッドで追加します。

  # 行を追加
  $row = $dataTable.NewRow()
  $row["ID"] = 1
  $row["Name"] = "Alice"
  $row["Age"] = 30
  $dataTable.Rows.Add($row)

  $row = $dataTable.NewRow()
  $row["ID"] = 2
  $row["Name"] = "Bob"
  $row["Age"] = 25
  $dataTable.Rows.Add($row)
  
  # DataTableの内容を表示
  $dataTable | Format-Table -AutoSize

 

DataSetの作成からDataTable作成までの解説

当項では、PowerShellでDataSetを作成し、そのDataSetからDataTableを作成する流れを解説します。

まずはNew-ObjectコマンドレットでSystem.Data.DataSetクラスにアクセスし、DataSetオブジェクトを作成します。

  # DataSetオブジェクトを作成
  $dataSet = New-Object System.Data.DataSet

次にNew-ObjectコマンドレットでDataTableオブジェクトを作成し、列の定義と行の追加をします。

  # DataTableオブジェクトを作成
  $dataTable = New-Object System.Data.DataTable
  
  # 列を追加
  $dataTable.Columns.Add("ID", [int])
  $dataTable.Columns.Add("Name", [string])
  $dataTable.Columns.Add("Age", [int])
  
  # 行を追加
  $row = $dataTable.NewRow()
  $row["ID"] = 1
  $row["Name"] = "Alice"
  $row["Age"] = 30
  $dataTable.Rows.Add($row)

DataSetオブジェクトのTable.AddメソッドでDataSetにDataTableを追加します。

  # DataSetにDataTableを追加
  $dataSet.Tables.Add($dataTable)
  
  # DataSet内の各テーブルを表示
  foreach ($table in $dataSet.Tables) {
      Write-Host "Table: $($table.TableName)"
      $table | Format-Table -AutoSize
  }

 

データベースに接続してSQLを実行しデータテーブルに格納するサンプル

当項では、前項で解説した「データベースへの接続」、「SQL文の埋め込みとヒア文字列」と「データセット及びデータテーブル」の内容も踏まえて、SQLを実行し、その実行結果をデータテーブルに格納するまでのサンプルコードを紹介します。

  # データベースとの接続情報を定義します。
  $serverName = "DBのIP又はホスト名"
  $databaseName = "データベース名"
  $username = "DBユーザー"
  $password = "ユーザーのパスワード"

  # 接続文字列を作成します。※SQL Server
  $connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;"

  # SQLクエリを定義します。
  $query = @"
  SELECT
  	*
  FROM
  	table_name
  "@

  # SQL Serverへの接続とクエリ実行のための.NETオブジェクトを作成します。
  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString

  # SqlDataAdapterとSqlCommandを作成
  $command = New-Object System.Data.SqlClient.SqlCommand
  $command.CommandText = $query
  $command.Connection = $connection

  $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $dataAdapter.SelectCommand = $command

  $dataSet = New-Object System.Data.DataSet
  $dataAdapter.Fill($dataSet) | Out-Null

  # 結果を表示
  $dataSet.Tables[0] | Format-Table -AutoSize

  # 接続を閉じる
  $connection.Close()

.NETのクラス「System.Data.SqlClient.SqlDataAdapter」を使用しています。
「SqlDataAdapter」はSQL Server用のSqlDataAdapterクラスです。
DataAdapterは大元のデータソースからデータを取得してDataSetやDataTableへ書き込みを担います。

SqlDataAdapterの「Fill」メソッドの引数にDataSetを渡せば、そのDataSet内のDataTableを作成してデータを格納してくれます。
また、同じく引数にDataTableを渡せば、そのDataTableにデータを格納してくれます。

尚、Fillメソッドでは、内部的に、

の一連の処理を実行してくれます。

Fill実行時にConnectionがOpenしていればOpenのまま、まだOpenされていなければ、処理終了後に自動的にCloseします。
よって、明示的にCoonectionをOpenしたり、Closeする必要はありませんが、上記コードでは念の為最後にCloseを入れています。

DataAdapterのFillメソッドの挙動については、以下のリンクに目を通してもらうとイメージし易いかと思います。

DataAdapter からの DataSet の読み込み - ADO.NET

データ ソースに依存しない一貫したリレーショナル プログラミング モデルを提供する ADO.NET の DataAdapter から、DataSet を設定する方法について説明します。

learn.microsoft.com

また、SqlDataAdapterでFillメソッドを実行時に、パイプラインで「Out-Null」に実行結果を渡しています。

Fillメソッドでは、データを読み込むと同時に、読み込んだ行数を返すのですが、このスクリプトではその行数を受け取る必要がないため、出力結果をOut-Nullに渡して不要な出力を削除しています。

  $dataAdapter.Fill($dataSet) | Out-Null

 

CSV出力処理

当項では、PowerShellでデータをCSVファイルに出力する処理を解説していきます。

尚、PowerShellでデータをCSVファイルに出力する場合は、コマンドレットの「Export-Csv」を使用するのが一般的です。

冒頭で説明したパイプラインを使用し、出力対象のオブジェクトをExport-Csvコマンドレットに渡して使用します。

Export-Csvコマンドレットの簡単な使用例としては、以下になります。

  Get-Process | Export-Csv -Path "C:\path\sample.csv"

このコードでは、Get-Processコマンドレットで取得した稼働中の全プロセスをExport-Csvコマンドレットに渡し、「-Path」パラメーターで出力先パスを指定して実行しています。

上記のリンク先にも説明はありますが、Export-Csvコマンドレットを利用するうえで、よく使用するパラメーターを以下で列挙します。

上記パラメーターのなかでも、まず、「-Path」は必ず必要です。

また、「-NoTypeInformation」を明示的にパラメーターに加えておかないと、出力したCSVファイルのヘッダ上部に、元データの情報が追記されてしまい邪魔なので、こちらもほぼ必須です。

また、「-Encoding」は、作成するCSVファイルの文字コードをShift-JISにしたい時は、必ず「Default」を指定します。

尚、「PowerShell 5.1」のExport-Csvコマンドレットでは、出力したCSVデータのすべてのフィールドは、引用符(ダブルクォーテーション)で囲まれて出力されます。

「PowerShell 7.0」以降からは、パラメーターに「-QuoteFields」が追加され、引用符で囲むか否かを切り替えできるようになりました。
よって、「PowerShell 5.1」でExport-Csvコマンドレットを使い限り、必ず引用符で囲まれたCSVデータしか出力できないため、引用符で囲まずに出力したい場合は別の方法を選択する必要があります。

今回の記事で解説をしている「データテーブルの内容をCSVで出力する」場合の処理では以下になります。

  # 文字コードにShift-JISを指定してデータテーブルをCSVファイルで出力します。
  $dataTable | Export-Csv -Path $outputCsv -NoTypeInformation -Encoding Default

 

PowerShellでデータベースを参照してCSVファイルに出力するサンプルコード

当記事でこれまで説明してきた内容を踏まえて、一連の処理を実装したサンプルコードを掲載します。

  # データベースとの接続情報を定義します。
  $serverName = "DBのIP又はホスト名"
  $databaseName = "データベース名"
  $username = "DBユーザー"
  $password = "ユーザーのパスワード"

  # 接続文字列を作成します。※SQL Server
  $connectionString = "Server=$serverName;Database=$databaseName;User Id=$username;Password=$password;"

  # SQLクエリを定義します。
  $query = @"
  SELECT
  	*
  FROM
  	table_name
  "@

  # SQL Serverへの接続とクエリ実行のための.NETオブジェクトを作成します。
  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString

  # SqlDataAdapterとSqlCommandを作成
  $command = New-Object System.Data.SqlClient.SqlCommand
  $command.CommandText = $query
  $command.Connection = $connection

  $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $dataAdapter.SelectCommand = $command

  # DataTableを作成
  $dataTable = New-Object System.Data.DataTable

  # データを取得してDataTableに格納
  $dataAdapter.Fill($dataTable) | Out-Null

  # 接続を閉じる
  $connection.Close()
  
  # DataTableをCSVファイルに出力
  $outputCsv = "C:\path\output.csv"  # 出力するCSVファイルのパス
  $dataTable | Export-Csv -Path $outputCsv -NoTypeInformation -Encoding UTF8
  
  Write-Output "データをCSVファイルに出力しました。: $outputCsv"

上記のコードでは、エラーハンドリング処理を入れていません。
必要によって処理を追加してください。
また、SQL Server と接続する場合のコードであり、他のRDBMSに接続する場合は、当記事の「データベースへの接続」の内容も踏まえて適切に変更してください。

 

最後に

今回の記事では、PowerShellを初めて触る人を対象に、PowerShellでプログラミングをするうえで知っておいてほしい基礎知識や、PowerShellからデータベースを参照したりCSVファイルを出力する場合に必要になる知識や実際の実装例を紹介してみました。

PowerShellは、Windows環境であれば新しいソフトをインストールすることなく利用できます。
また、PowerShellは最近のモダンなプログラミング言語と同等の機能を有しており、様々な処理を実装することが可能です。

今回の記事がどなたかの参考になれば幸いです。

それでは皆さまごきげんよう。