##概要
「SQLステートメントの実行」を用いたExcelデータの操作方法の紹介です。
オンプレミスで自動処理を行う場合、フローにExcelデータの加工を組み込む場面があるかと思います。
PowerAutomateDesktopにはExcel操作アクションがいくつか用意されていますが、用意されたアクションで足りない場合は別の手段を使うことになります。
「列と行」のExcelデータに対してSQL文を使うことで、複数条件でのフィルター処理など標準アクションでは難しい部分を補うことができます。
通常のアクションだけでは困ったときに役立てればと思って書きました。
##注意事項と前提条件
PowerAutomateDesktopはプレビュー版のため仕様が変更される可能性があります。
2020年11月の情報になります。
2021年8月に更新しました
- PowerAutomateDesktop
（プレビュー）バージョン2.1.20312.195722.12.171.21216 - Windows10Pro
20H221H1
参考
WinAutomation Examples SQL With Excel
##デモシナリオ
マイクロソフトのPower BI 用の Excel の財務サンプル ブックを使わせていただきます。
入力した値(Year)でフィルターをかけ、Salesの値を集計してメッセージに表示します。
Excelを開かずPAD内ですべて処理します
##準備
マイクロソフトのPower BI 用の Excel の財務サンプル ブックのダウンロードから「財務サンプル Excel ブック」をダウンロードしてデスクトップに置いておきます。
https://docs.microsoft.com/ja-jp/power-bi/create-reports/sample-financial-download
##フローを作成
適当な名前を付けてフローを作成する
####1. 特別なフォルダーを取得
####2. 入力ダイアログの表示
フィルターの数値入力変数の設定（今回はYear)用
####3. デスクトップにおいたサンプルExcelファイルのパスを設定
変数名：%ExcelPath%
宛先：%SpecialFolderPath%\Financial Sample.xlsx
####4. SQL接続を開く OLE DBプロバイダー接続文字列を設定
OLE DBはマイクロソフトの設計したAPIです。
APIに接続するための文字列を設定します。
%ExcelPath%の部分は3で設定したExcelファイルのパスが代入されます。
変数名デフォルト %SQLConnection%
接続文字列
Provider=Microsoft.ACE.OLEDB.16.0;Data Source=%ExcelPath%;Extended Properties="Excel 12.0 Xml;HDR=YES";
####5. SQLステートメントの実行を設定
接続の取得方法: SQL接続変数
接続文字列: %SQLConnection%
今回は入力したYearの値でフィルターを行いSalesを合計したいのでSQLステートメントは以下のように設定します。
Select sum([ Sales]) as [合計]
from [sheet1$]
where Year = "%UserInput%"
注意 サンプルデータのSales列名はSの前に半角スペースが開いています。[ ]内に記述するときに同様に記述しないとエラーが出ます。
変数名は%QueryResult%自動的に設定されます。
%QueryResult%に返されるデータは2次元配列（行と列データ）になります。
列名を除いたデータ部分が返されます。
Excelと違いindexが0から始まります。
ExcelでいうA1セルは%QueryResult[0][0]%となります。
%QueryResult[行index][列index]%の表記になっています。
Excel(A1) = %QueryResult[0][0]% Excel(B1) = %QueryResult[0][1]%
Excel(A2) = %QueryResult[1][0]% Excel(B2) = %QueryResult[1][1]%
のような対応関係です。
####6.SQL接続を閉じる
####7.メッセージの表示
最後に集計値をメッセージで表示します。
##完成
今回のプロセスは以下の通りとなります。
空のフローを作成後、以下のコードを貼りつけて試すこともできます。
以下のコードを実行するには サンプルxlsxをデスクトップに置いて下さい。
Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
Display.InputDialog Title: $'''PowerAutoDesktop''' Message: $'''集計年を入力してください''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> UserInput ButtonPressed=> ButtonPressed
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 sum([ Sales]) as [合計]
from [sheet1$]
where Year = \"%UserInput%\"''' Timeout: 30 Result=> QueryResult
Database.Close Connection: SQLConnection
Display.ShowMessage Title: $'''PowerAutomateDesktop''' Message: $'''%UserInput%年の売上は＄%QueryResult[0][0]%です。''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
##まとめ
構造化されたExcelデータに対してSQLステートメントを実行することでExcel標準アクションでは行えないフィルター処理などの操作が可能です。
Insert や Update等も使えるので活用の幅が広がると思います。
SQL編その2に続きます。
追記 2021年8月ループを使用しないフローに変更しました。