PAD: Excel からデータ取得する際はやっぱりSQLですね

背景

PAD(Power Automate Desktop) を使い始めて Sample を社内向けに作成し始めたところ
Excel の Action をそのまま使うとやっぱり重いなぁと思い、SQL で高速化しようというお話。

とりあえずのゴールは、Select のみ。Insert/Update またいずれ

取得対象のExcel

A1にごみを入れて、これを外して取得するために、Table取得したかったんですが・・方法が分からなかったので、Sheet＋Cell範囲指定で取得してます😂

今回やりたかったけど、できなかったこと

1. Table取得
   1. これが出来たら、無駄な範囲指定無くせるんですけどね・・
2. Query 取得 (Power Query)
   1. Tableと同様
3. Tableとかの一覧取得
   1. 指定方法が見えてくるんじゃないかなぁと思い、Excelをソースとして渡して結果取得したかったんだけど・・

フロー例

二種類の Provider での接続例

1. 接続Excel のPathを変数へ格納
2. Connection String の生成
3. SQL 実行

OleDB

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%ExcelFilePath%;Extended Properties="Excel 12.0 Xml;HDR=YES";

ODBC

Provider=MSDASQL;Persist Security Info=False;Mode=Read;Extended Properties="DSN=Excel Files;DBQ=%ExcelFilePath%;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;";

RetreiveExcelDataBySQL

SET ExcelFilePath TO $'''C:\\Users\\UserName\\Downloads\\SampleData.xlsx'''
Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%ExcelFilePath%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Connection=> SQLConnection
Database.Execute Connection: SQLConnection Statement: $'''Select * From [DataTable$B2:F]''' Timeout: 4 Result=> ResultWithoutEndRowNumber
Database.Execute Connection: SQLConnection Statement: $'''Select * From [DataTable$B2:F] DataT ''' Timeout: 4 Result=> ResultAliasTableName
Database.Execute Connection: SQLConnection Statement: $'''Select * From [DataTable$B2:F] DataT Where DataT.Column1 > 5''' Timeout: 4 Result=> ResultWithWhere
Database.Connect ConnectionString: $'''Provider=MSDASQL;Persist Security Info=False;Mode=Read;Extended Properties=\"DSN=Excel Files;DBQ=%ExcelFilePath%;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;\";''' Connection=> SQLConnection
Database.Execute Connection: SQLConnection Statement: $'''Select *  From [DataTable$B2:F12] Where [Column4]  is not NULL''' Timeout: 4 Result=> QueryResultDataOnly

補足

1. Table 指定方法
   • [{Table Name}$]
     • ex.) [DataTable$]
   • [{Table Name}${Cell範囲指定}]
     • ex.) [DataTable$B2:F8]
     • ex.) [DataTable$B2:F] 最終行番号を省略してお任せすることも可能
2. Table の別名
   • 'as' 無しで、定義名書くだけ
     • ex.) Select * From [DataTable$B2:F] DataT
3. ヘッダー認識設定
   1. HDR=YES

Connection String とかは 検索すれば色々出てくると思うので割愛

あとがき

PADは便利ですよね。
市民開発者増えていって SQL が誰しもが使う未来が来るのかな？と思うと
来年の新人教育に PAD + SQL も入れようかな。

参考

Keyword

how to retreive Excel Data by SQL