【ExcelVBA】CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする方法について

10分

SHARE

本記事では、CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする方法についてご説明します。

【動画】ExcelのマクロからSQL ServerのテーブルデータだけをCSVファイルに出力する実際の動き

本題に入る前に、まずはCSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする実際の動きについて、次のツイートをご覧ください。

ツイート内の動画では、CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートしています。

目次へ戻る

本記事ではCSVファイルのデータをインポートするのにBULK INSERTコマンドを利用します

本記事では、CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートするのに、BULK INSERTコマンドを利用します。

BULK INSERTコマンドとは、SQL Serverのコマンドの一つで、データファイル(CSVファイルやテキストファイルなど)をSQL Serverのテーブルにインポートすることができるコマンドです。

今回はこのBULK INSERTコマンドを実行するストアドプロシージャを用意しておき、そのストアドプロシージャをExcelのマクロが呼び出すことで、CSVファイルのデータをSQL Serverのテーブルにインポートします。

【参考】
BULK INSERTコマンドに関するMicrosoftのリファレンスサイト

参考 BULK INSERT (Transact-SQL)BULK INSERTに関するMicrosoftのリファレンスサイト
目次へ戻る

【課題と解決方法】BULK INSERTコマンドは条件に合致したデータだけをインポートすることはできない

BULK INSERTコマンドはCSVファイルのデータをSQL Serverのテーブルにインポートするのにとても便利ですが、BULK INSERTコマンドは条件に合致したデータだけをインポートすることはできません。
(CSVファイルの取り込み開始行、または最終行の位置を指定することはできますが)

例えば、文字列「〇〇」が含まれているデータだけインポートする、数値「〇〇」以上のデータだけインポートするなど、条件に合致したデータだけをインポートすることはできません。

そこで、次の方法で課題を解決します。

STEP.1
一時テーブル生成
一時テーブル生成します。
STEP.2
BULK INSERTコマンドを使って、STEP.1の一時テーブルにCSVファイルのデータをインポート
BULK INSERTコマンドを使って、STEP.1の一時テーブルにCSVファイルのデータをインポートします。
STEP.3
条件に合致するインポートデータを抽出するSelect文を用意
条件に合致するインポートデータを抽出するSelect文を用意します。
STEP.4
STEP.3のselect文で一時テーブルから抽出したデータを、対象のテーブルに挿入
STEP.3のselect文で一時テーブルから抽出したデータを、対象のテーブルに挿入します。

対象テーブルにはCSVファイルのデータはインポートはせず、一時テーブルにBULK INSERTでCSVファイルのデータをインポートさせ、その一時テーブルから条件に合致するデータだけを取り出して対象のテーブルに挿入させるというわけです。

以上の流れを踏まえたうえで、次項からはCSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする方法について詳しくご説明していきます。

目次へ戻る

CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする方法

ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力するには、次の流れの通りにコードを書いていきます。

Excelのマクロ側

STEP.1
DBへの接続情報取得
DBへの接続情報を取得します。
ストアドプロシージャを実行するにはまずはDBに接続しなければなりませんが、接続する情報(ユーザ名やパスワードといった接続情報)がなければDBに接続できません。
STEP.2
SQL Serverに接続
STEP.1で取得した接続情報をもとに、SQL Serverに接続に接続します。
SQL Serverに接続に接続するには、ConnectionオブジェクトのOpenメソッドを使います。
STEP.3
BULK INSERTコマンドを実行するストアドプロシージャを呼び出すのに必要な情報の設定
BULK INSERTコマンドを実行するストアドプロシージャを呼び出すのに必要な情報を設定します。
設定する情報は、次の通りです。
・SQL Serverのデータソースに対してストアドプロシージャを実行する場合に定数「adCmdStoredProc」を指定
・(BULK INSERTコマンドを実行する)ストアド名
STEP.5
ストアドプロシージャに渡すパラメタの設定
ストアドプロシージャに渡すパラメタの設定を行います。
設定するものは、次の通りです。
・パラメタ名
・パラメタの型
・パラメタの値

なお本記事では、ストアドプロシージャに次の4つのパラメタを渡しています。
①CSVファイル名
②挿入する条件(SQL)
⇒条件に合致するデータを、この「挿入する条件(SQL)」で指定します。
③データを挿入する対象のテーブル名
⇒条件に合致するデータだけをインポートするテーブルです。
④一時テーブル名
⇒BULK INSERTコマンドでCSVファイルのデータをインポートする一時テーブルです。
処理の最後にDROPコマンドで削除します。(ストアドプロシージャ内で削除)

STEP.6
ストアドプロシージャの呼び出し
ストアドプロシージャの呼び出します。

ストアドプロシージャ側

STEP.1
Excelのマクロ側から渡された値を受け取る
Excelのマクロ側から渡された値を受け取ります。
本記事では、受け取る値は次の通りです。
①CSVファイル名
②挿入する条件(SQL)
③データを挿入する対象のテーブル名
④一時テーブル名
STEP.2
CSVファイルのデータをインポートする前に、データを挿入する対象のテーブルを削除
CSVファイルのデータをインポートする前に、データを挿入する対象のテーブルを削除しておきます。
STEP.3
データを挿入する対象のテーブルを元に、一時テーブルを作成
データを挿入する対象のテーブルを元に、一時テーブルを作成します。
STEP.4
BULK INSERTコマンドを実行し、CSVファイルのデータを一時テーブルにインポート
BULK INSERTコマンドを実行し、CSVファイルのデータを一時テーブルにインポートします。
STEP.5
データを挿入する対象のテーブルに一時テーブルのデータを挿入
データを挿入する対象のテーブルに一時テーブルのデータを挿入します。
挿入する際は、条件に合致するデータを条件に指定して挿入します。
STEP.6
一時テーブルを削除
一時テーブルを削除します。
目次へ戻る

ソースコード

Excelのマクロのコード(例)

Excelのマクロのコード(例)は次の通りです。

    '変数定義
    Dim DBName As String, connDB As String, CSVFile As String, prmNameValAry() As Variant, cnt As Integer
    Dim objConnection As ADODB.Connection
    Dim objCommand As ADODB.Command
    Dim objParameter As ADODB.Parameter

    'データベース接続情報を取得
    DBName = "testDataDB"
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"
    
    'パラメタ名とパラメタ値を配列に格納
    '①CSVファイル名
    '②挿入する条件(SQL)
    '③データを挿入する対象のテーブル
    '④一時テーブル名
    prmNameValAry = Array( _
                        Array("insertDataFilePath", _
                              "conditionsStr", _
                              "insToTbl", _
                              "tmpTbl" _
                              ), _
                        Array(ThisWorkbook.Path & "\" & Range("D2").Value, _
                              Range("D3").Value, _
                              Range("D4").Value, _
                              Range("D5").Value _
                        ) _
                    )

    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB

    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "dbo.sp_std_info"         'ストアドプロシージャ名
 
    'ストアドプロシージャのパラメタに渡す値の設定(渡したいパラメタの数だけ繰り返す)
    For cnt = 0 To UBound(prmNameValAry(0), 1)

        'パラメタオブジェクトの作成
        Set objParameter = objCommand.CreateParameter()
    
        'パラメタ名(ストアド側の変数名と合わせること)
        objParameter.Name = prmNameValAry(0)(cnt)
    
        'データ型の指定(ここでは文字列を指定)
        objParameter.Type = adWChar
    
        'CSVファイル名(の文字列)のサイズ指定
        objParameter.Size = Len(prmNameValAry(1)(cnt))
    
        'ストアドプロシージャの引数に渡す値
        objParameter.Value = prmNameValAry(1)(cnt)
    
        '入力パラメータ
        objParameter.Direction = adParamInput
    
        'ストアドプロシージャの引数に渡す値を設定する
        objCommand.Parameters.Append objParameter
    
    Next
    
    'ストアドプロシージャの呼び出し
    objCommand.Execute

    '後処理
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing

コードの解説

注目すべきコード①

最初に見て頂きたいのは70行目です。

この70行目でストアドプロシージャを呼び出しています。

注目すべきコード②

次に見て頂きたいのは19行目から30行目です。

ストアドプロシージャに渡したいパラメタ4つをここで用意しています。

  • ①CSVファイル名
  • ②挿入する条件(SQL)
  • ③データを挿入する対象のテーブル名
  • ④一時テーブル名

※このコードの記述はあくまで一例です。ストアドプロシージャに渡したいパラメタ4つが用意出来ればどんな書き方でも構いません。

注目すべきコード③

呼び出すストアドプロシージャ名は41行目で指定し、47行目から65行目でストアドプロシージャに渡すパラメタの設定を行います。

設定するものは、次の通りです。

・パラメタ名
・パラメタの型
・パラメタの値

以上のストアドプロシージャに渡すパラメタの設定を、ストアドプロシージャに渡すパラメタの数だけ繰り返し行っています。(パラメタが4つあるので4回繰り返しています)

ストアドプロシージャのコード(例)

ストアドプロシージャのコード(例)は次の通りです。

USE [testDataDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_std_info]

	--Excel側から渡された引数
	 @insertDataFilePath			NVARCHAR(100)	--CSVファイル名(フルパス) 
	,@conditionsStr					NVARCHAR(100)	--(テーブル挿入の)条件
	,@insToTbl						NVARCHAR(100)	--データを挿入する対象のテーブル
	,@tmpTbl						NVARCHAR(100)	--一時テーブル

AS
BEGIN

	SET NOCOUNT ON;

	--変数定義
	DECLARE @SQL			NVARCHAR(1000);		--SQL文
	DECLARE @RtnCd			INT;				--エラーコード
    DECLARE @RtnMsg			VARCHAR(2000);		--エラーメッセージ

	--変数の初期化
	SET @SQL      = '';

	BEGIN TRY

		BEGIN TRANSACTION;
	
		--CSVファイルのデータをインポートする前に、データを挿入する対象のテーブルデータを削除する
		SET @SQL = N' delete from ' + @insToTbl;
		EXEC sp_executesql @SQL;

		--(データを挿入する対象のテーブルを元に)一時テーブルを作成
		SET @SQL = N' SELECT * INTO ##' + @tmpTbl + ' FROM ' + @insToTbl;				   
		EXEC sp_executesql @SQL;

		--CSVファイルを一時テーブルにインポートする
		SET @SQL = N' BULK INSERT ##' + @tmpTbl +
				   N' FROM ''' + @insertDataFilePath + '''' +		--Excel側から渡されたCSVファイル
				   N' WITH' +
				   N' (' +
				   N'  FIELDTERMINATOR  = '',''' +					--データがカンマ区切り
				   N' ,ROWTERMINATOR = ''\n''' +					--終端が改行コード(\n)
				   N' ,FIRSTROW = 2 ' +								--インポートするデータの開始行
				   N' );'
				   
		EXEC sp_executesql @SQL;
	
		--一時テーブルのデータを、データを挿入する対象のテーブルに条件に合致するデータだけを挿入する
		SET @SQL = N' insert into ' + @insToTbl + ' ' + @conditionsStr;				   
		EXEC sp_executesql @SQL;
	
		--一時テーブルを削除する
		SET @SQL = N' drop table ##' + @tmpTbl;
		EXEC sp_executesql @SQL;

		COMMIT TRANSACTION;

	    SET @RtnCd = 0;
    
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT <> 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
 
        SET @RtnCd	= ERROR_NUMBER();
        SET @RtnMsg = ERROR_MESSAGE();
    END CATCH;
END
GO

コードの解説

注目すべきコード①

最初に見て頂きたいのは56行目です。

一時テーブルのデータを、データを挿入する対象のテーブルに条件に合致するデータだけを挿入しています。

一時テーブルのデータから、条件に合致するデータだけを抽出し、そのデータを挿入する対象のテーブルに挿入しています。

合致する条件(文)はExcelのマクロから渡されて、変数「@conditionsStr」に格納されています。

注目すべきコード②

次に見て頂きたいのは40行目です。

ここで一時テーブルを作成しています。

一時テーブルを作成するのにCreate文は使わず、「SELECT * INTO」を使ってデータを挿入する対象のテーブルを元に一時テーブルを作成しています。

つまり、データを挿入する対象のテーブルをコピーして一時テーブルを作成しているというわけです。

テーブルの構造は全く同じで一時テーブルを用意したかったので、「SELECT * INTO」を使った方が楽ですし、コード数も少なく済むため「SELECT * INTO」を使って一時テーブルを作成しています。

注目すべきコード③

最初に見て頂きたいのは44行目から51行目です。

このコードがBULK INSERTコマンドを実行するためのコードです。

変数「SQL」にBULK INSERTコマンドを実行するためのコードを格納し、53行目で実行します。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのExcelのマクロのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

  1. Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、先ほどのコードの3行目の「ADODB.Connection」と4行目の「ADODB.Command」、5行目の「ADODB.Parameter」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。

    Dim objConnection As ADODB.Connection
    Dim objCommand As ADODB.Command
    Dim objParameter As ADODB.Parameter

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、CSVファイルのデータの中から条件に合致するデータだけをSQL Serverのテーブルにインポートする方法についてご説明しました。

CSVファイルのデータをSQL Serverのテーブルにインポートするのに、BULK INSERTコマンドを利用するのは便利ではあるものの、条件に合致するデータだけをインポートすることはできないため処理を工夫しなければなりません。

どのように工夫するのかというと、ザックリとした処理の流れとしては、

  • ①一時テーブルの生成
  • ②BULK INSERTコマンドを使って、①の一時テーブルにCSVファイルのデータをインポート
  • ③条件に合致するインポートデータを抽出するSelect文を用意
  • ④ ③のselect文で一時テーブルから抽出したデータを、対象のテーブルに挿入

以上になります。

あくまで今回紹介した方法は一つの例ですが、こんなやり方があるのかと参考にしていただけたら幸いです。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】ExcelのマクロからSQL Server…

次の記事

【ExcelVBA】ExcelのマクロからMicrosoft …