ADODBのSQLの特殊な構文を使ってエクセルからEXCELやACCESSのデータにアクセスします
サンプルコード
Sheet1のA1セルに以下のデータを貼り付けて下さい
| 名前 | 科目 | 点数 |
|---|---|---|
| 鈴木 | 英語 | 95 |
| 鈴木 | 数学 | 59 |
| 佐藤 | 英語 | 71 |
| 佐藤 | 数学 | 97 |
Sheet2のA1セルに以下のSQLを入力してください
SELECT 名前
, SUM(点数) AS 合計点
FROM [Sheet1$A1:C5]
GROUP BY 名前
VBAの標準モジュールに以下のコードを貼り付けて下さい
Option Explicit
Private Const C_CONNECTSTR = "Provider={Provider};Data Source={FileName};Extended Properties={Properties}"
Private Const C_PROVIDER = "Microsoft.ACE.OLEDB.12.0"
'Private Const C_PROVIDER = "Microsoft.Jet.OLEDB.4.0"
Private Const C_PROPERTIES = "Excel 12.0"
'Private Const C_PROPERTIES = "Excel 8.0"
Private Const adOpenStatic = 3
Private Const adLockReadOnly = 1
'*****************************************************************************
'サンプルコード
'*****************************************************************************
Public Sub Test()
Call MsgBox("一度も保存されていないファイルはエラーとなるのでご注意ください")
Dim strSQL As String
Dim objOutRange As Range
strSQL = Range("Sheet2!A1") 'SQLの入力されたセル
Set objOutRange = Range("Sheet2!B1") 'SQLの実行結果を表示する左上のセル
Dim strCon As String
strCon = C_CONNECTSTR
strCon = Replace(strCon, "{Provider}", C_PROVIDER)
strCon = Replace(strCon, "{FileName}", ActiveWorkbook.Name)
strCon = Replace(strCon, "{Properties}", C_PROPERTIES)
'SQLの実行結果のレコードセットを取得
Dim objRecordset As Object
Set objRecordset = CreateObject("ADODB.Recordset")
Call objRecordset.Open(strSQL, strCon, adOpenStatic, adLockReadOnly)
'結果を表示
Dim i As Long
For i = 1 To objRecordset.Fields.Count
'見出し設定
objOutRange.Cells(1, i) = objRecordset.Fields(i - 1).Name
Next
'明細設定
Call objOutRange.Cells(2, 1).CopyFromRecordset(objRecordset)
End Sub
ファイルを 必ず保存 してから、マクロの
Test() を実行してください
Sheet2のB1セルに以下のデータが表示されれば成功です
| 名前 | 合計点 |
|---|---|
| 鈴木 | 168 |
| 佐藤 | 154 |
SQLの構文について
作業中のEXCELブックのデータの指定方法
SELECT *
FROM [Sheet1$]
--セル範囲を指定しない時は、データが入力された範囲がテーブルの領域として認識されます
--[]と$は必須
SELECT *
FROM [Sheet1$A1:C5]
--セル範囲を指定した時は、そのセル範囲がテーブルの領域として認識されます
UPDATE [Sheet1$A:C] --列のみを指定することも可能です
SET 列1 = 値
-- UPDATE文でセルの値を更新することも可能です
外部ファイルのデータの指定方法
[外部データ接続子]を記述することで外部ファイルのデータに対してSQLを実行することが可能です
SELECT *
FROM [外部データ接続子].[テーブル名]
[外部データ接続子]のバリエーション
1. EXCELファイルの指定方法
[EXCEL 12.0;DATABASE=ファイルパス\ファイル名]
[EXCEL 8.0;DATABASE=ファイルパス\ファイル名]
SELECT *
FROM [EXCEL 12.0;DATABASE=c:\tmp\sample.xlsx].[Sheet1$]
SELECT *
FROM [EXCEL 12.0;DATABASE=c:\tmp\sample.xlsb].[Sheet1$]
--大容量のデータを扱う場合、xlsb(バイナリ)ファイルで保存した方がxlsxファイルで
--保存するより最大で5分の1ほどファイルサイズが小さくなります
2. Accessファイルの指定方法
[MS ACCESS;DATABASE=ファイルパス\ファイル名]
[MS ACCESS;DATABASE=ファイルパス\ファイル名;PWD=パスワード] パスワードの指定も可能です
SELECT *
FROM [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[テーブル名]
SELECT *
FROM [MS ACCESS;DATABASE=c:\tmp\sample.accdb].クエリ名 --[]は省略可
--クエリも可
INSERT UPDATE DELETE 等で値を更新することも可能です
--以下のようにEXCELのデータを既存のテーブルに追加することも可能です
INSERT INTO [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[テーブル名]
SELECT *
FROM [Sheet1$]
CREATE TABLE DROP TABLE 等のDDL文の実行も可能です
--以下のようにEXCELのデータを新たなテーブルにインポートすることも可能です
SELECT *
INTO [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[新テーブル名]
FROM [Sheet1$]
--以下の様にクエリを作成することも可能です
CREATE VIEW [MS ACCESS;DATABASE=c:\tmp\sample.mdb].[新クエリ名] AS
select_statement
3. CSV(TXT)ファイルの指定方法
[TEXT;DATABASE=ファイルパス]
SELECT *
FROM [TEXT;DATABASE=c:\tmp].[sample.txt]
--[テーブル名]にはファイル名を指定します
4. ODBC接続
筆者の環境では、試せていないので接続できるかどうかは不明です。
以下のサイトを参照して紹介しています。
外部参照するデータベースの識別
実際に試された方がおられましたら、ぜひコメントお願いします。
またSQLの方言は該当のデータベースのそれではなくAccessとなります。
オラクル
[ODBC;Driver={Microsoft ODBC for Oracle};SERVER=サーバ名;UID=ユーザID;PWD=パスワード]
SQLServer
[ODBC;Driver={SQL Server};SERVER=サーバ名;Database=ＤＢ名;UID=ユーザID;PWD=パスワード]
など
2020/05/23追記
MySQLにて以下の識別子で接続できることを確認しました。
[ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;DATABASE=ＤＢ名;UID=ユーザID;PWD=パスワード]
5. 参考
当記事は下記サイトのIN句の別の記述方法です。
IN 句 (Microsoft Access SQL)
ADOでの異なる種類のファイルへのSQL
外部参照するデータベースの識別
SQLの方言
SQLの書式は、AccessのSQLに準拠します。
以下に主な特徴を紹介します。
1.文字列連結
文字列の連結には & を使用します。
リテラルの文字列は 「"」ダブルコーテーション または 「'」シングルコーテーション で囲います。
SELECT 列1 & 列2 & "123" & 'ABC' AS 連結例
FROM [Sheet1$]
2.条件判定
条件の判定には IIF()関数 または SWITCH()関数 あるいは CHOOSE()関数を使用します。
IIF(条件, THENの値, ELSEの値)
あるいは
IIF(条件, THENの値)
SWITCH(条件1, 値1, 条件2, 値2, ... , TRUE, その他の値)
--最後の条件をTRUEとすることで、条件を満たさなかった時の値を指定することができます
--EXCEL2016以降のIFS()関数と同様
CHOOSE(項目, 値1, 値2, ... )
--EXCELのCHOOSE()関数と同様
--項目=1の時は値1、項目=2の時は値2 ...
IIF()関数でELSE部分を省略した場合はNULLとなります。
よって以下の2行は同じ結果になります。
IIF(条件, THENの値)
IIF(条件, THENの値, NULL)
SWITCH()関数・CHOOSE()関数についても、いずれの条件も満たさない場合はNULLとなります。
余談
集計関数にIIF()関数を使用するテクニックをご紹介します
例1：点数が0点を除いた最小点を取得する場合
MIN(IIF(点数>0,点数)) 0点の場合はNULLとなるため集計関数からは無視される
例2：点数が100点のレコードをカウントする
COUNT(IIF(点数=100,点数)) 100点以外の場合はNULLとなるため集計関数からは無視される
または SUM(IIF(点数=100,1,0))
さらにどうでもいい余談、上記のCOUNTを使用する場合とSUMを使用する場合で一見結果は同じ様ですが微妙な違いがあります。
データベースから返ってくるRecordsetのデータ型がCOUNTは正数型 SUMは実数型となります。
例えばSELECT INTOでAccessに新規テーブルを登録した場合、COUNTを使用したカラムは整数型、SUMを使用したカラムは実数型となります。
3.NULLの判定
項目 IS NULL または ISNULL(項目)
NULL以外の判定
項目 IS NOT NULL または NOT ISNULL(項目)
SELECT IIF(ISNULL(列1), '-', 列1) AS 列1
, IIF(NOT ISNULL(列2), 列2, '-') AS 列2
, IIF(列3 IS NULL, "-", 列3) AS 列3
, IIF(列4 IS NOT NULL, 列4, "-") AS 列4
FROM [Sheet1$]
WHERE ISNULL(列5)
OR NOT ISNULL(列6)
OR 列7 IS NULL
OR 列8 IS NOT NULL
4.数値項目へのキャスト(型変換)
以下の例の様に1を乗じることで容易に数値として認識させることができます。
SELECT *
FROM [Sheet1$]
WHERE 項目*1 IN(1,2,3)
--EXCELのデータに対してSQLを実行した場合、数値と思っていても文字列と判定されてしまう場合が良くあります
--そのような場合にこの手法が有効です
SELECT *
FROM [Sheet1$]
WHERE RIGHT(年月日, 2)*1 IN(5,15,25)
--YYYYMMDDの書式で設定されている日付項目の日の部分を数値で評価しています
5.予約語や数字から始まる名称など、カラム名として不適切な項目の対処法
項目名を[]で囲みます。
SELECT [KEY], [1番目], [氏 名]
FROM [Sheet1$]
6.外部結合
SELECT Z.*, A.*
FROM [Sheet1$] AS Z
LEFT JOIN
[Sheet2$] AS A
ON Z.コード1 = A.コード1
AND Z.コード2 = A.コード2
-- ZとAの部分はテーブルに別名を設定したい場合に記述します。名称は自由に選択できます
-- ZのテーブルのレコードはAのテーブルの内容にかかわらず全レコード出力されます
AccessのSQLにFULL JOINはありません。
7.内部結合
SELECT Z.*, A.*
FROM [Sheet1$] AS Z
INNER JOIN
[Sheet2$] AS A
ON Z.コード1 = A.コード1
AND Z.コード2 = A.コード2
-- ZとAの部分はテーブルに別名を設定したい場合に記述します。名称は自由に選択できます
-- ZのテーブルとAのテーブルの両方に存在するレコードのみ出力されます
以下のSQLも同様の結果になります。
SELECT Z.*, A.*
FROM [Sheet1$] AS Z
, [Sheet2$] AS A
WHERE Z.コード1 = A.コード1
AND Z.コード2 = A.コード2
8.複数カラムのIN演算子
複数カラムに対するIN演算子の判定はできません
SELECT *
FROM [Sheet1$]
WHERE (列1, 列2) IN(SELECT 列1, 列2
FROM [Sheet2$])
--上記はエラーとなります
--ORACLEなどでは有効な記述方法です
代わりにEXISTSを使います
SELECT *
FROM [Sheet1$] AS Z
WHERE EXISTS(SELECT *
FROM [Sheet2$] AS A
WHERE Z.列1 = A.列1
AND Z.列2 = A.列2)
または複数カラムを連結して１つのカラムにする
SELECT *
FROM [Sheet1$]
WHERE 列1 & 区切り文字 & 列2 IN(SELECT 列1 & 区切り文字 & 列2
FROM [Sheet2$])
--区切り文字はなくても良いと思いますが
--列1と列2の桁数が可変であれば、なんらかの区切り文字があった方が確実です
あるいはJOINを使用する
SELECT Z.*
FROM [Sheet1$] AS Z
LEFT JOIN
(SELECT DISTINCT --注1
列1,列2
FROM [Sheet2$]) AS A
ON Z.列1 = A.列1
AND Z.列2 = A.列2
WHERE NOT ISNULL(A.列1)
または
WHERE ISNULL(A.列1) -- NOT INのケース
--注1 DISTINCTはサブクエリのレコードに重複がある場合は必須です
--なければ元のZテーブルのレコード数より多くのレコードが結果に出力されます
筆者の体験によれば、JOINを使用する方法が、他の方法に比べてダントツに高速です。
もし、INまたはEXISTSを使用したSQLの実行に非常に時間がかかるようであれば、JOINの使用をおすすめします。
9.コメント
コメントは記述できません。
一般的なSQLでは、-- は行末までコメント /* */ に囲まれている文字はコメントとなりますが、AccessのSQLでは使用できません。
ただし、以下で紹介しているツールではコメントの記述ができるように対応しています。
10.サブクエリの記述
AccessのSQLではWITH句は使えません。
ただし、以下で紹介しているツールでは、セル参照という方法を使って、サブクエリ部分を切り出して記述することが可能です。
#ツールの紹介
この記事で紹介した手法を用いて、EXCELでかんたんにSQLを実行できるツールを作成しました。
以下の記事で紹介していますので、ぜひ一読ください。
EXCELでEXCELファイルおよびACCESSのデータに対してかんたんにSQLを実行するツール