はじめに
ExcelのPower QueryでネイティブSQLを発行しDBデータを取得します。
SQLと接続文字列はワークシート上のセル内に仕込み、M言語は必要最低限にします。
M言語
PowerQueryエディターの詳細エディタで定義されるM言語のソースは以下のような感じです。
let
source = Excel.CurrentWorkbook(){[Name="Properties"]}[Content],
settings = Table.TransformColumnTypes(source,{{"Property", type text}, {"Value", type text}}),
connectionString = Table.SelectRows(settings, each ([Property] = "CONNECTIONSTRING")){0}[Value],
sqlStatement = Table.SelectRows(settings, each ([Property] = "SQL")){0}[Value],
result = Odbc.Query(connectionString, sqlStatement)
in
result
- ODBCを使ってます
- Excel上でPropertiesという名前のテーブルを作成しM言語からアクセスします
- 新しいバージョンのExcelでは範囲でもいけるようです
Propertiesテーブル
Excelのセル上に定義するPropertiesテーブルは以下のような感じを想定してます。内容は適当です。
| Property | Value |
|---|---|
| SQL | select saleId, productId, productName, saleDate, saleAmount from Sale join Product using(productCode) |
| CONNECTIONSTRING | Driver={Oracle in OraDb11g_home1};DBQ=HOGE |
以上です。（長くなるので実装手順は省略しました。知りたい人がいれば書くかもしれません...）
終わりに
ExcelからDBに接続する方式はいろいろありましたが、Power Queryはずいぶん簡単になりました。VBAを書かなく済むのもいい感じです。
ネイティブSQLはあまり推奨されてませんが、セル上のSQLで何やっているか確認できますし、AzureやPythonにロジックを外だしするときも移管が便利なケースがありますので、実装の選択肢の一つになります。
なおネイティブSQLはデフォルトではセキュリティ系のエラーが出ますので対応方法はマニュアルや他の方の記事をご参照ください。