Power QueryからSQLiteへ ODBCとDSNの接続構造

LWP | Power QueryからSQLiteへ ODBCとDSNの接続構造

Power QueryからSQLiteへ ODBCとDSNの接続構造

図解1|ブックの外にも、接続に必要なものがある

コイ丸先輩とシャケモンが、ExcelとM、ODBCドライバーマネージャー、SQLite用ドライバー、DBファイル、DSN設定の関係を確かめる

図1|Excelブック、接続設定、DB本体は、それぞれ別のものです。

解説1|Mが同じでも、接続環境まで同じとは限らない

ストーリー

「このブックを渡せば、次のPCでも動くかな」。引き継ぎ用のフォルダーを作っていたコイ丸先輩は、Excelのファイルを一つ入れました。

シャケモンが手を止めます。「Power Queryの接続設定と、SQLite用のドライバーも確かめましょう」。ブックの外にも必要なものがあると聞いて、先輩はフォルダーをもう一度開きました。

接続を支えるもの

Power QueryのMは、どこから何を取得し、どう変換するかを記述します。ODBCドライバーマネージャーが接続を仲介し、SQLite用ODBCドライバーがDBファイルを扱います。売上データそのものは、ブックとは別のsales.dbに入っています。

DSNはData Source Nameの略で、接続設定に付ける名前です。たとえばSalesSQLiteというDSNに、使うドライバーとDBの場所を設定します。DSNの名前をMへ書いても、その名前から参照する設定が別PCへ自動で移るわけではありません。

今回の範囲

WindowsデスクトップExcelを対象に、接続の構造と設定の置き場所を整理します。サンプルSQLの結果はSQLiteで確認し、SQLite ODBC経由の接続、Power Queryの実更新、別PCでの再現は未実施として区別します。

図解2|Excelとドライバーのビット数を合わせる

32bitと64bitのExcelに対応するODBCドライバーを合わせ、64bit Windowsの管理ツールの場所とUser DSN表示の注意を示す

図2|Windowsのビット数だけで、Excelやドライバーのビット数は決まりません。

解説2|まずExcel側の32bitか64bitかを確かめる

合わせる対象

32bitのExcelから使うなら対応する32bitドライバー、64bitのExcelから使うなら64bitドライバーが必要です。64bit Windowsに32bit Excelが入っている構成もあります。先にExcelの[アカウント]からバージョン情報を開き、Excel側のビット数を確かめます。

「PCが64bitなら全部64bit、とは言えないんだね」。コイ丸先輩は、引き継ぎメモへExcelとドライバーを別々に書きました。

管理ツールも分かれる

64bit Windowsでは、32bit用ODBCデータソース管理ツールはC:\Windows\SysWOW64\odbcad32.exe、64bit用はC:\Windows\System32\odbcad32.exeにあります。ファイル名はどちらもodbcad32.exeです。フォルダー名と役割を合わせて読みます。

Excelプロセス

対応ドライバー

DSN作成に使う管理ツール

32bit

32bit

SysWOW64側

64bit

64bit

System32側

一覧に見えるだけでは決めない

User DSNは、32bit用と64bit用の両方の管理ツールに表示される場合があります。DSN名を見つけても、使うドライバーがExcelと合っているかは別に確認します。詳細はMicrosoftのODBC管理ツールの説明にあります。

図解3|接続情報を、どこへ置くか

DSN型は接続設定名をMへ、DSN-less型はドライバー等の接続情報をMへ書き、両方とも対応ドライバーとDBが必要であることを比較する

図3|DSNを使わない方式でも、ドライバーと接続先は必要です。

解説3|DSN型とDSN-less型では、設定の持ち方が違う

設定名で参照する

DSN型では、Mへdsn=SalesSQLiteのような指定を残します。ドライバーやDBの場所はDSN側で管理します。配布先には、その名前で利用できるDSNと、対応するドライバー、参照先のDBが必要です。

接続情報を直接書く

DSN-less型では、DriverやDBの場所などを接続文字列へ書きます。図中のDriver={製品名};Database=DBの場所;は構造を示す模式例です。実際のDriver表示名、使えるキー、値の書き方は採用したドライバーで確認します。

「DSNがないなら、準備もなし?」。先輩の問いに、シャケモンがドライバーの欄を指しました。「ここは、どちらにも必要です」。設定名を使わないことと、接続環境が不要になることは別です。

パラメーターを使う場合

DBパスなどをパラメーターで受け取れば、M本文を書き換える場所を減らせます。ただし、DSN型またはDSN-less型の接続条件は残ります。セミコロンなどの区切りを含む値は、単純連結でそのまま接続文字列へ渡せるとは限りません。

Power QueryのODBCコネクター資料は、DSN指定後の追加属性を含めないと説明しています。DSNにDatabase属性を足して上書きする例は使わず、追加情報はDSN側で設定します。Microsoftの接続文字列の説明

図解4|DSNの3種類は、置き場所が違う

User DSN、System DSN、File DSNの設定場所と確認点を比べ、設定ファイルと売上DB本体が別であることを示す

図4|設定を共有する方法と、データ本体を参照する条件を分けて考えます。

解説4|誰が、どの設定を使うのか

設定の置き場所

User DSNは利用者ごと、System DSNは端末側の設定です。File DSNは、接続情報をファイルへ保存します。同じDSNという名前でも、置き場所と利用できる範囲が違います。

種類

確かめること

User DSN

更新を実行する利用者が、その設定を使えるか

System DSN

Excelとドライバーのビット数、実行する環境が合うか

File DSN

設定ファイルに加え、対応ドライバーと接続先があるか

ファイルに入っているもの

「この設定ファイルも渡せばいい?」。コイ丸先輩は、DBとは別のファイルを指しました。「設定を渡しても、DB本体は別です」。シャケモンは、sales.dbの欄を残します。

File DSNは接続先などの情報を保存するもので、SQLiteドライバーや売上データを丸ごと含むものではありません。Excelの接続ファイルのエクスポートとFile DSNも、同じものとして扱いません。

今回の主例は、既に用意されたSalesSQLiteというDSNへ接続する形に絞ります。File DSNを使ったPower Query接続の実証は行っていません。DSN管理の概要はMicrosoftのデータソース管理を参照してください。

図解5|一覧を返す入口と、SQLを実行する入口

Odbc.DataSourceは表やビューを選ぶナビゲーション表、Odbc.Queryは指定したSQLの結果表を返す。後続Mのfoldingの違いも分ける

図5|戻り値がtableでも、表を選ぶための一覧と売上の明細は別です。

解説5|関数の名前と、返ってくる表の中身を結び付ける

表を選ぶための一覧

Odbc.DataSourceは、接続先にあるSQLの表やビューを選ぶための表を返します。Odbc.DataSource("dsn=SalesSQLite")だけで、Salesの売上行を取得したとは限りません。まず一覧から対象を選ぶ、という関係です。

図では、Name列とData列を持つナビゲーションの模式表を示しています。実際の階層や列の現れ方は、ドライバーの返す情報や設定で変わります。Odbc.DataSourceの関数仕様

SQLの結果を直接受け取る

Odbc.Queryは、接続文字列とSQLを受け取り、そのSQLをODBCで実行した結果を返します。今回はこの入口を使い、Salesから条件に合う二行を選びます。Odbc.Queryの関数仕様

「表の名前が並ぶのと、売上の行が並ぶのは別なんだね」。先輩は、取得する対象を引き継ぎメモへ加えました。

SQLに書く条件とfolding

Mで指定した変換をデータベース側の処理へ変換することを、クエリfoldingと呼びます。Odbc.DataSourceではドライバーの能力などに依存します。Odbc.QueryはSQLを渡す入口で、後続M変換のfoldingを提供しません。SQLへWHEREを書くことと、Mのフィルターが自動でSQLへ変換されることを区別します。MicrosoftのODBC拡張機能の説明

図解6|SQLで、条件に合う2行を選ぶ

一行を一売上とするSalesの三行から、WHERE Amount >= 1000で1200と2000の二行を選び、SaleDateの昇順へ並べる

図6|売上として存在することと、今回の抽出条件に合うことは別です。

解説6|一行の意味と、抽出する条件をそろえる

入力例

Salesの一行は一つの売上を表し、SaleDateは日付の文字列、Amountは整数金額です。2026-09-01が500、09-02が1200、09-03が2000の三行を使います。

これは実データと分けた再現用の例です。DBを用意する場合は、新規の試用DBへ次のSQLを実行します。既存の売上表へ追記する指示ではありません。

01

CREATE TABLE Sales (SaleDate TEXT NOT NULL, Amount INTEGER NOT NULL);

02

INSERT INTO Sales VALUES ('2026-09-01', 500);

03

INSERT INTO Sales VALUES ('2026-09-02', 1200);

04

INSERT INTO Sales VALUES ('2026-09-03', 2000);

抽出するSQL

01

SELECT SaleDate, Amount

02

FROM Sales

03

WHERE Amount >= 1000

04

ORDER BY SaleDate;

WHEREはAmountが1000以上の行を選び、ORDER BYはSaleDateの昇順を指定します。500の売上が消えるのではなく、今回の結果に含まれなくなります。結果は09-02の1200と09-03の2000の二行です。行順を必要とするため、並べ替えもSQLへ書いています。SQLiteのSELECT仕様

図解7|値の型と、件数・合計を確かめる

SQLで選んだ二行をMで日付型と64bit整数へ整え、行数二行・合計3200・日付順を照合する

図7|DBに保存された値の表現と、Mで扱う型を分けて読みます。

解説7|SQLで選び、Mで型を整える

接続準備後に使うM

SalesSQLiteというDSNが試用DBへ接続するよう準備された後に使う例です。sales_rowsがSQLの取得結果、typed_salesが型を整えた表です。

01

let

02

    sales_rows = Odbc.Query(

03

        "dsn=SalesSQLite",

04

        "SELECT SaleDate, Amount FROM Sales WHERE Amount >= 1000 ORDER BY SaleDate"

05

    ),

06

    typed_sales = Table.TransformColumnTypes(

07

        sales_rows,

08

        {{"SaleDate", type date}, {"Amount", Int64.Type}},

09

        "en-US"

10

    )

11

in

12

    typed_sales

関数の役割と型

Odbc.Queryで二行を取得し、Table.TransformColumnTypesでSaleDateをdate、AmountをInt64.Typeへそろえます。en-USは型変換時のカルチャ指定です。SQLiteには日付専用の保存クラスがなく、今回のDBではISO形式の文字列を使います。ドライバーから返る型も確認した上で、M側の出力型を決めます。SQLiteの日付の扱い

結果と読むポイント

この例の期待値は二行、1200+2000=3200、日付は09-02、09-03の順です。サンプルSQLの結果はSQLiteだけで照合済みですが、ODBC経由のM更新は未実施です。接続を準備した環境では、同じ件数・値・型を別に照合してください。

図解8|接続の問題と、結果0行を分ける

DSNとドライバー、DBの場所と権限、表と列、抽出条件の四層を切り分け、エラーと正常実行して零行の結果を区別する

図8|結果が空でも、どの層で何が起きたかを確かめます。

解説8|見つからない対象と、条件に合わない行は別

確認する対象を分ける

接続に使うDSNやドライバー、参照するDBファイル、DB内の表と列は別の対象です。エラーが出たときはメッセージを残し、まずどの対象を見つけられないのかを確かめます。

確認対象

確認する内容

DSN・ドライバー

設定名、実行する利用者、製品、ビット数

DBファイル

絶対パス、目的のファイルか、参照権限

表・列

Sales、SaleDate、Amountが存在するか

抽出条件

Amount >= 1000に合う行があるか

0行の結果を読む

「0行なら、接続失敗かな?」。先輩が空の表を見ていると、シャケモンは条件を指しました。「正常に実行できても、条件に合う行がない場合はあります」。

たとえば今回の三行に対し、条件をAmount >= 3000へ変えると、該当する行はありません。SQLiteでこの結果が0行になることを照合しました。接続エラーを、一律に空の表へ置き換えてしまうと、この違いが分からなくなります。

ファイルの取り違えにも注意する

誤ったDBパスの扱いはドライバー次第で、新しい空DBが作られる場合もあります。接続できたという事実だけで、正しい売上DBを開いているとは判断できません。ファイルの場所と、必要な表・列の両方を確かめます。

図解9|次のPCへ、接続条件も引き継ぐ

使うソフト、接続設定、参照する実体、照合する結果の四分類で引き継ぎ条件を残し、確認済みと実接続未実施を分ける

図9|次の担当がコードと環境を別々に確かめられるようにします。

解説9|動かす条件と、確かめる結果を残す

まとめ

ExcelブックにMが入っていても、ODBCドライバーやDSN、DBファイルまで一緒に入るわけではありません。接続情報をDSN側へ置くか、M側へ書くかを選び、その方式で必要になる環境をそろえます。

引き継ぎメモには、Excelとドライバーの製品・版・ビット数、DSNの種類と名前、DBの絶対パスと参照権限、Power Queryのデータソース設定を残します。加えて、この例なら二行・合計3200という、正しい接続先を読んだか確かめる材料を添えます。

「ブックだけじゃなく、使う条件も残そう」。コイ丸先輩がメモを戻すと、シャケモンは、DBの場所をもう一度見ました。「次は、どこを確かめるか分かりますね」。

今回の仕掛け

取得処理はM、接続先などの設定はDSN、売上データはSQLiteのDBファイルが受け持ちます。DSNを使わず、接続情報をM側へ書く方式もあります。選んだ方式に合わせて、コードと環境条件を組み合わせて確かめます。

確認したのは、M関数・ODBCの一般仕様と、SQLite上のサンプルSQLです。SQLite用ODBCドライバーの導入、DSN登録、Power Query実更新、別PCでの再現は行っていません。図中の設定画面や接続経路は模式図です。

出典と利用について

MicrosoftのODBCコネクター、Odbc.DataSource、Odbc.Query、SQLiteのSELECT・型を参照しました。2026-10-28確認。特定ドライバーの導入手順や接続文字列の保証は含みません。

文章・構成:LWP 山中一弘。図版はLWPのキャラクター設定を参照し、内蔵画像生成で制作しました。コードとデータは説明用の小例です。

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。