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

##概要
[前回の記事 Power Automate Desktop「SQLステートメントの実行」でExcelデータを操作する](https://qiita.com/sys_zero/items/2a68575d6ccd82bed902)の続きです。
このアクションに使えるSQLのサンプル例文を作成しました。
Winautomationのサンプルを参考にPower Automate Desktopに合わせ試しています。

##注意事項と前提条件
2020年12月の情報になります。
Power Automate Desktop バージョン2.2.20339.22608
Windows10Pro 20H2

##excelファイル別接続文字列
excelファイルの形式に応じてextended propertiesの値を変える必要がある
例は.xlsの場合
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/8f74a784-c79c-d8ef-6916-0fff429ae262.png)


```
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 の財務サンプル ブック](https://docs.microsoft.com/ja-jp/power-bi/create-reports/sample-financial-download)を使わせていただきます。ダウンロードしてデスクトップに置いて頂けますとフロー全体をコピペすることで試すことができます。

### 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を表示
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/fb171011-f45a-eab1-e40a-2e7c9a5113b4.png)

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

```
SELECT [Country],COUNT([Country])
FROM [sheet1$]
GROUP BY [Country] 
```
国名でデータ数をカウント集計
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/5cd88de3-04de-d820-de28-2b75201fb14c.png)

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

```
UPDATE [sheet1$]
SET [Country] = "Japan",[Year]="2012"
WHERE [Year] = "2014"
AND [Country] ="Canada"
```
2014年Canadaのデータを2012年JAPANに書換える
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/42f6d475-efad-a923-5289-2aae2d48b76d.png)
2.と同じ集計をしているがUPDATEでの変更が確認できる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/ee13b62f-2b58-6c3c-d3dc-c2ad16ac2b94.png)


### 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)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/db2c9810-89d6-c47c-97f0-97b21664c981.png)

## フロー全体像とコード
コードはフローを新規作成してコピペ可能
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/680039/e32eb839-b2ba-3deb-9860-b6db055258d8.png)


```
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や複数条件も入れてみたので試していただければ幸いです。