PowerShellのExcel・CSV自動化の知識マップ(知識グラフ)
PowerShellでCSV・Excel業務を自動化する際の最大の落とし穴は文字コードで、Windows PowerShell 5.1とPowerShell 7で既定のエンコーディングが異なるため、Import-CsvとExport-Csvは読み書きの両方でエンコーディングを明示しないと文字化けが起こり得ます。集計はGroup-Objectでグループ化してからMeasure-Objectで合計するのが基本形で、2つのCSVの突合はCompare-Objectによる差分検出か、マスタをハッシュテーブル化した結合かを目的に応じて使い分けます。xlsxの出力はExcel本体が不要なImportExcelモジュールが第一候補で、Excel COM操作はCOM参照の解放漏れによるEXCEL.EXEの残留を招きやすく、Microsoftが無人環境でのOffice自動化を推奨していないため、タスクスケジューラでの定期実行には向きません。
Import-Csv
CSVファイルを、ヘッダー行を列名とする「1行=1オブジェクト」の表としてPowerShellへ読み込むコマンドレット。値はすべて文字列として読み込まれる。
Import-Csvコマンドレット
Export-Csv
パイプラインのオブジェクトをCSVファイルへ書き出すPowerShellのコマンドレット。
ImportExcelモジュール
PowerShell Galleryで配布されるコミュニティ製モジュールで、Excel本体をインストールせずにxlsxの読み書き・テーブル化・書式設定・ピボットテーブル作成ができる。
PowerShell
オブジェクトパイプラインとエラー処理機構を持つ、Microsoftが最も堅牢で最新のWindows自動化に推奨するコマンドラインシェル兼スクリプト言語。
CSVの文字化け
CSVを読み書きするアプリやツール(ExcelやPowerShellのImport-Csv/Export-Csvなど)が文字コードを誤認したり既定エンコーディングと入力が食い違ったりする(BOMなしUTF-8をANSIとして解釈する、Export-Csvの既定ASCIIエンコーディングで書き込むなど)ことで、日本語が正しく表示されない、または失われる現象。
文字コードの誤認による文字化け
-Encodingの明示指定
Import-Csv/Export-Csvなどの読み書きで、コマンドレットごとの既定値に頼らず、-Encodingでエンコーディングを明示的に指定すること。
エンコーディングの明示
Group-Object
指定したプロパティの値ごとにオブジェクトをグループ化し、件数や内容を集計できるPowerShellのコマンドレット。
Measure-Object
パイプラインを流れるオブジェクトの件数・合計・平均・最大・最小を集計するPowerShellのコマンドレット。
Compare-Object
2つの一覧を比較し、片方にしかない要素をSideIndicatorとともに返すPowerShellのコマンドレット。
ハッシュテーブルによる列の引き当て(結合)
マスタ側をキー→行のハッシュテーブルにしてから、明細を1行ずつ引き当てる、SQLのJOINに相当するPowerShellの実装パターン。
ハッシュテーブル結合
Get-FileHash
ファイルのハッシュ値(既定SHA256)を計算し、配布物やバックアップ、重複候補の内容が同一かを確認できるPowerShellのコマンドレット。
xlsx出力
セルの書式を出力側で制御できる、xlsx形式でのファイル出力。CSVの値解釈問題を根本的に回避できる。
Excelファイル出力
TLS 1.2の明示的な有効化
[Net.ServicePointManager]::SecurityProtocolでTLS 1.2を有効にする設定。Windows PowerShell 5.1では既定で有効でない場合がある。
Excel COM自動化(Excel COM Interop)
C#などの外部プログラムからCOM経由でExcelアプリケーション本体を起動・操作する自動化方式。
EXCEL.EXEのプロセス残留
Quit()を呼んでもCOM参照(RCW)が残っているために、EXCEL.EXEなどのプロセスが終了せずに残り続ける状態。
EXCEL.EXEが終了しない問題
Marshal.ReleaseComObject
RCWの内部参照カウントをデクリメントし、0になった時点でRCWが握るCOM参照を即座に解放する.NETのAPI。公式には絶対に必要な場合にのみ使うことが推奨される。
タスクスケジューラの無人実行
指定したアカウントで、スケジュールやログオンなどのトリガーに従いプログラムを実行するWindowsの仕組み。ログオン種別(対話トークン・パスワード保存・S4Uなど)の設定によっては、ユーザーがログオンしていなくても実行できる。
PowerShellはImport-CsvでCSVをオブジェクトの表として読み込める
PowerShellはExport-Csvでオブジェクトの列をCSVに書き出せる
Windows PowerShell 5.1のImport-CsvはBOMのないファイルをUTF-8と解釈するため、Shift_JISのCSVをそのまま読むと文字化けし得る
Windows PowerShell 5.1のExport-Csvの既定エンコーディングはASCIIで、-Encodingを付け忘れると保存の時点で日本語が失われる
読み書きの両方で-Encodingを明示すれば、5.1と7で既定エンコーディングが異なることによる文字化けは避けられる。ただし入力バイト列や受け取り側と合わないコーデックを選べば文字化けは起こり得る
PowerShellはGroup-Objectでオブジェクトをグループ化できる
PowerShellはMeasure-Objectで件数や合計などを集計できる
部署ごとの集計のような処理は、Group-Objectでグループ化してから各グループをMeasure-Objectで合計する順で行う
PowerShellはCompare-Objectで2つの一覧の差分を検出できる
PowerShellはハッシュテーブルを使ってマスタと明細の列を引き当てる結合処理を実装できる
PowerShellはGet-FileHashでファイルのハッシュ値を計算できる
xlsxを直接読み書きしたい場合、PowerShellはPowerShell Galleryで配布されるImportExcelモジュールを追加導入することで読み書きできる
ImportExcelはExcel本体のインストール不要でxlsxの読み書き・テーブル化・書式設定・ピボットテーブル作成までできるため、xlsx出力の第一候補になる
PowerShell GalleryからのInstall-ModuleにはTLS 1.2以上が必要で、既定で有効でない環境ではセッションで明示的に有効化しないと失敗することがある
通常のxlsx出力にExcel COM操作を用いることは推奨されない。マクロ実行などExcelそのものの機能が必要な場合の最終手段に限る
ワークブックやセル範囲を触るたびに増えるCOM参照(RCW)を解放しないと、Quit()を呼んでもEXCEL.EXEのプロセスが残留する。中間オブジェクトのRCWまで解放していれば残留しない
Marshal.ReleaseComObjectによる明示的解放とGC.Collectの実行が、EXCEL.EXEの残留を軽減する
Excel COM操作でEXCEL.EXEの残留を防ぐには、触った中間オブジェクトも含めて参照を明示的に解放することが前提になる
Microsoftは無人・非対話のクライアント(サービスやタスクスケジューラでの実行など)からのOffice自動化を推奨せずサポートもしていないため、Excel COM操作をタスクスケジューラでの無人実行に用いることは推奨されない