はじめに
こちらの記事は大量のExcelデータから特定行の抽出を行うにあたり試行錯誤した内容です。
あくまで思考錯誤なので、他に良いやり方があると思いますが、ご容赦ください。
前提条件
- 使用するExcelは以下データ（500,000行×5列）
- 抽出する行は学籍番号＝500000（末尾のデータ）
最初に思いついた方法
シンプルな方法です。
※既にExcelを開いている状態でフローを実行するため「実行中のExcelに添付」を使ってます。
「適用するフィルター」（フィルターデータテーブル）は[学籍番号] = '500000'を指定します。
実行すると読込むのに5秒掛かりました。
次に思いついた方法
ステップは「最初に思いついた方法」の倍（6ステップ）に増えてます。
「PowerShellスクリプトの実行」で、学籍番号＝500000のExcel行番号を変数「PowershellOutput」に出力して、ピンポイントでExcelワークシートから１行読込む内容です。
PowerShellスクリプトの実行のPowerShell コードは以下のとおりです。
こちらのコードで学籍番号：500000を検索してExcel行番号を返却します。
# 既に起動しているEXCELを取得。
$excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
# 開いているExcelの中から「student_data_5x500k.xlsx」を指定
$wb = $excel.Workbooks.Item("student_data_5x500k.xlsx")
# Sheet1を取得
$ws = $wb.Worksheets.Item("Sheet1")
# 1列目(A列)を指定
$col = $ws.Columns.Item(1)
# Findで500000を検索。
$found = $col.Find("500000",$col.Cells.Item(1),-4163,1)
# 結果判定
if ($found -ne $null) {
# 行番号を返却
$found.Row
}
else {
"NotFound"
}
Findの引数の設定内容は以下のとおりです。
| 内容 | 値 | 備考 |
|---|---|---|
| 検索する値 | "500000" | - |
| 検索開始位置 | $col.Cells.Item(1) | A1セル |
| 検索方向 | -4163 | xlNext(横方向) |
| 検索方式 | 1 | xlWhole(完全一致) |
PowerShellスクリプトの出力結果は、変数「PowershellOutput」に格納されますが、テキスト値のため数値にします。
「Excelワークシートから読み取る」で変数「TextAsNumber」を使って、検索した学籍番号：500000の1行をExcelワークシートから取得します。
「範囲の最初の行に列名が含まれています」をオフにしていますので、取得したデータの列名はColumn1～Column5となっています。※データ１（検索１行）
そこで再度「Excelワークシートから読み取る」を使います。
「先頭列」をA「先頭行」を1「最終列」をE「最終行」を1とし「範囲の最初の行に列名が含まれています」をオンにすることで、データなしの列名のみのテーブルを取得します。
データは列名のみの空のテーブルが取得できます。※データ２（列名のみ）
最後に「行をデータ テーブルに挿入する」を使ってデータ２（列名のみ）に対して、データ１（検索１行）を追加します。
「新しい値」にはList型とDatarow型のみ指定でき、逆にDataTable型は指定不可のため「%ExcelData%」は不可です。「%ExcelData[0]%」としてDataRow型で指定します。
もっとスマートな方法が
sys_zeroさんの「PowerAutomateDesktop「SQLステートメントの実行」でExcelデータを操作する」の記事を拝見してもっとスマートにできると感銘を受けました。
記事を拝見して、実際以下のようなフローを作成してみました。
フローの詳細内容は今回割愛します。
実行結果は3秒でした。勝手にすみません(__)
こちらも速い。しかもEXCELファイルをあらかじめ起動する必要が無いです。
ステップ数も4ステップで済むし、保守性も高まります。
この方法を是非使いたいと心底思ったのですが、仕事場では「SQLステートメントの実行」がセキュリティの観点から使えないことが分かり、泣く泣く「PowerShell スクリプトの実行」を使いました。
Excelにアクセスする場合限定で「SQLステートメントの実行」使わしてもらえないか、今後交渉したいと思います。
まとめ
データテーブルやExcelアクションだけでなく「SQLステートメントの実行」や「スクリプトの実行」を使うことで大量データの場合に処理時間を短縮することができると今回分かりました。
実行時間はそれぞれ条件が異なり、また用途や実現したい事によって時間もそれぞれ変わってくるので、一概には判断できないところもあるのですが、自分なりの試行錯誤と詳しい方々の知見を参考にしたいです。