##概要
前回の記事 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や複数条件も入れてみたので試していただければ幸いです。