Power Automate Desktop ExcelファイルをSQLで操作する
Excelデータからデータテーブルに読み込んで操作するならSQL接続を使うと便利かも
はじめに
以前、PADでデータテーブルを操作するのにこんなにアクションが少なくて何が出来るの？
と言いました。……そこまでは言ってなかったかな？
ともかく、SQLを使えば良かったんですね。
ExcelデータをSQLで操作する方法について、下記の記事が大変参考になりました。
Zero Sys(@sys_zero)さん、ありがとうございました。
参考
公式サイト
この記事はここでおしまいです。あとはおまけ。
詰まったところ
あいまい検索
パターンマッチングしたくて以下のコードを記述したら0件になって悩みました。
(*) 使えないの？
(*) じゃなくて (%) でした。上の記事のその2に書いてありました。
SELECT * FROM [%Sheet%$] WHERE [列名] LIKE "*中央区"
SELECT * FROM [%Sheet%$] WHERE [列名] LIKE "%%中央区"
しかも、PADで (%) は変数を表す特殊文字として使用されているので、パターン文字列の (%) を (%) でエスケープする必要があって (%%) になると。どっと疲れました。
っていうかなんで%？ *で良くない？
自分の知っているのはこれ。
| 文字 | 説明 |
|---|---|
| * | 任意の数の文字 |
| ? | 任意の1文字 |
| # | 任意の1文字の数字 |
| [文字リスト] | 文字リスト内の1文字 |
| [!文字リスト] | 文字リスト以外の1文字 |
| [文字1-文字2] | 文字1～2の範囲の1文字 |
| [!文字1-文字2] | 文字1～2の範囲以外の1文字 |
……いま手持ちの本を見てたら 重要 表記で書いてありました。[1]
- ANSI-89は、通常のデータベースファイルに対してクエリを実行するときや「DAO」というオブジェクトを用いてデータにアクセスする際に使用されます。
- ANSI-92は、Microsoft SQL Serverに接続されたデータベースファイルに対してクエリを実行するときや「ADO」というオブジェクトを用いてデータにアクセスする際に使用されます。
| ANSI-89 | ANSI-92 | 説明 |
|---|---|---|
| * | % | 任意の数の文字 |
| ? | - | 任意の1文字 |
| [!文字リスト] | [^文字リスト] | 文字リスト以外の1文字 |
日付の比較
関数で日付を入力する方法はわかりやすい。
SELECT * FROM [%Sheet%$] WHERE [列名] < DATE()
ついでに
改行する／しないは好きに。
SELECT * FROM [%Sheet%$]
WHERE [F22] = "未登録"
AND [F21] < DATE()
「列番号」は、Column1～ではなくF1～です。実際に動かしてフロー変数を見て確認しましたが、書籍の方にもExcelブックへの接続の項目で「列番号」について書いてありました。
「列番号」には、抽出条件に指定するフィールドを「F+番号」の形式で記述します。たとえば、1列目のフィールドなら「F1」を、2列目のフィールドなら「F2」を記述します。[2]
ヘッダー
ヘッダーに改行があるデータをこれで読み込んだ場合に、改行なしになってデータテーブルになるのは何気に良いなと思いました。
Excelのアクションで読み込んだ場合は、改行ありのままヘッダーになってその後の処理で困るので。
そもそもヘッダーで改行使わないでって話なんですけどね。
良いなと書いたけど、CSVデータの頭から数行に余計な記述があって
HDR=YESでSQL接続を開けないファイルが多いのが困ったものです。
実際に使ってみると……
ここまで試してきて、いざ実際に使ってみようとするとうまくいきませんでした。SQL以外の要因で。
業務で利用するファイルは秘密度ラベルで情報管理を適切に設定することが必須になっていて、そういったファイルはSQL接続ではエラーになり開けませんでした。
こういうの。
情報
一旦Excelを開いてデータを読み取り→新規ブックに書き込んで保存→SQL接続を開く→SQLステートメントの実行→SQL接続を閉じる→ブックを削除→みたいにすると出来そう。
あとは他の方法でなんとかするしか。
あとがき
SQL接続が使えればExcelを開いて読み取るよりもサクッと行けてとても良さげなので、データテーブルを操作する手段のひとつとしてSQL良いかも。と思いました。サクッと行けてないですが。
引用した書籍のリンク
Discussion