PowerAutomateDesktop「SQLステートメントの実行」でExcelデータを操作する その2

概要

前回の記事 Power Automate Desktop「SQLステートメントの実行」でExcelデータを操作するの続きです。
このアクションに使えるSQLのサンプル例文を作成しました。
Winautomationのサンプルを参考にPower Automate Desktopに合わせ試しています。

注意事項と前提条件

2020年12月の情報になります。
Power Automate Desktop バージョン2.2.20339.22608
Windows10Pro 20H2

excelファイル別接続文字列

excelファイルの形式に応じてextended propertiesの値を変える必要がある
例は.xlsの場合

Provider=Microsoft.ACE.OLEDB.12.0;data source=%ExcelPath%;extended properties="Excel 8.0;HDR=Yes";

https://www.microsoft.com/en-us/download/details.aspx?id=54920
Microsoft Access Database Engine 2016 RedistributableのダウンロードページのInstall Instrucionsに記述あり

File Type (extension) Extended Properties

Excel 97-2003 Workbook (.xls) "Excel 8.0"
Excel Workbook (.xlsx) "Excel 12.0 Xml"
Excel Macro-enabled workbook (.xlsm) "Excel 12.0 Macro"
Excel Non-XML binary workbook (.xlsb) "Excel 12.0"

Power Automate Desktop「SQLステートメントの実行」で使えるSQLサンプル例文

マイクロソフトのPower BI 用の Excel の財務サンプル ブックを使わせていただきます。ダウンロードしてデスクトップに置いて頂けますとフロー全体をコピペすることで試すことができます。

1. Query1 SELECT

全列を指定したいときは*アスタリスク
列指定するときは,カンマで区切って[]で列名を追加
WHERE節は　AND　OR　等でフィルタ条件を追加可能
条件が文字列ならば""が必要

likeも通るが現状のバージョンでは\%が使えないため部分検索はできない
Power Automate Desktopでは\%がエスケープできていない現象と思われます。
WinAutomation、Robinでは可

2021/3/15追記　SQL内に%%表記が可能なのを確認したため部分検索を行えます。

SELECT [ Sales]
FROM [sheet1$]
WHERE [Year]="2013"
AND [Country]="Canada"

2013年CanadaのSalesを表示

2.Query2 COUNT GROUP BY

COUNT()丸括弧の中に[]で列名を記述しないとエラーになる

SELECT [Country],COUNT([Country])
FROM [sheet1$]
GROUP BY [Country] 

国名でデータ数をカウント集計

3.Query3 UPDATE

SET節は,カンマで区切って書換え箇所の指定と内容を増やせる
EXCELデータを書換えるだけなので返すクエリはない

UPDATE [sheet1$]
SET [Country] = "Japan",[Year]="2012"
WHERE [Year] = "2014"
AND [Country] ="Canada"

2014年Canadaのデータを2012年JAPANに書換える

2.と同じ集計をしているがUPDATEでの変更が確認できる

4.Query4 INSERT INTO

VALUESは文字列は""で囲うが数値は必要ない
EXCELテーブルに追加されるわけではなくデータとして最終行に追加される

INSERT INTO [sheet1$]([Segment],[Country],[Product],[Discount Band],[Units Sold],[Manufacturing Price],[Sale Price],[Gross Sales])
VALUES("a","b","c","d",111,222,333,444)

フロー全体像とコード

コードはフローを新規作成してコピペ可能

Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
SET ExcelPath TO $'''%SpecialFolderPath%\\Financial Sample.xlsx'''
Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.16.0;data source=%ExcelPath%;extended properties=\"Excel 12.0 xml;HDR=yes\";''' Connection=> SQLConnection
Database.Execute Connection: SQLConnection Statement: $'''SELECT [ Sales]
FROM [sheet1$]
WHERE [Year]=\"2013\"
AND [Country]=\"Canada\"''' Timeout: 30 Result=> QueryResult1
Display.ShowMessage Title: $'''1.SELECT''' Message: QueryResult1 Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
Database.Execute Connection: SQLConnection Statement: $'''SELECT [Country],COUNT([Country])
FROM [sheet1$]
GROUP BY [Country] ''' Timeout: 30 Result=> QueryResult2
Display.ShowMessage Title: $'''2.COUNT GROUP BY''' Message: QueryResult2 Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
Database.Execute Connection: SQLConnection Statement: $'''UPDATE [sheet1$]
SET [Country] = \"Japan\",[Year]=\"2012\"
WHERE [Year] = \"2014\"
AND [Country] =\"Canada\"''' Timeout: 30 Result=> QueryResult3
Display.ShowMessage Title: $'''3.UPDATE''' Message: $'''データを書き換えるだけなので返すクエリはありません''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed3
Database.Execute Connection: SQLConnection Statement: $'''SELECT [Country],COUNT([Country])
FROM [sheet1$]
GROUP BY [Country] ''' Timeout: 30 Result=> QueryResult4
Display.ShowMessage Title: $'''UPDATE確認です''' Message: QueryResult4 Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed4
Database.Execute Connection: SQLConnection Statement: $'''INSERT INTO [sheet1$]([Segment],[Country],[Product],[Discount Band],[Units Sold],[Manufacturing Price],[Sale Price],[Gross Sales])
VALUES(\"a\",\"b\",\"c\",\"d\",111,222,333,444)''' Timeout: 30 Result=> QueryResult5
Display.ShowMessage Title: $'''4 INSERT INTO''' Message: $'''返すクエリはありません。EXCELファイルの最終行に書き込みがされているか確認して下さい''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed5
Database.Close Connection: SQLConnection

まとめ

デスクトップレコーダーでポチポチ作るのは私は結構難しいと感じています。
SQLステートメントの実行アクションはEXCEL操作の標準アクションの補完にありがたい機能です。
なんといってもポチポチに比べるとフローが短くて済みます。

WinautomationのサンプルにはないCOUNT,GROUP BYや複数条件も入れてみたので試していただければ幸いです。