【ExcelVBA】ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートするには？

6分

SHARE

本記事では、ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートする方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

CSVファイルのデータインポートはBULK INSERTコマンドで

ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートするには、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のリファレンスサイト
目次へ戻る

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

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

ツイート内の動画では、ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートしています。

目次へ戻る

ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートする方法

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

Excel側のマクロ

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

ストアドプロシージャ側

STEP.1
Excel側のマクロから渡されたCSVファイルを受け取る
Excel側のマクロから渡されたCSVファイルを受け取ります。
STEP.2
BULK INSERTコマンドを実行し、CSVファイルのデータをインポートする
BULK INSERTコマンドを実行し、CSVファイルのデータをインポートします。
目次へ戻る

ソースコード

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

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

    '変数定義
    Dim DBName As String, connDB As String, CSVFile As String
    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ファイル
    CSVFile = ThisWorkbook.Path & "\データ1.csv"

    '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_scoreList"         'ストアドプロシージャ名

    'パラメタオブジェクトの作成
    Set objParameter = objCommand.CreateParameter()

    '引数の名前(ストアド側の変数名と合わせること)
    objParameter.Name = "insertDataFilePath"

    'データ型の指定(ここでは文字列を指定)
    objParameter.Type = adWChar

    'CSVファイル名(の文字列)のサイズ指定
    objParameter.Size = Len(CSVFile)

    'ストアドプロシージャの引数に渡す値(CSVファイル名)
    objParameter.Value = CSVFile

    '入力パラメータ
    objParameter.Direction = adParamInput

    'ストアドプロシージャの引数に渡す値を設定する
    objCommand.Parameters.Append objParameter
    
    'ストアドプロシージャの呼び出し
    objCommand.Execute

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

コードの解説

注目すべきコード①

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

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

注目すべきコード②

次に見ていただきたいのは15行目です。

インポートするCSVファイル名の指定を15行目で行っています。

注目すべきコード③

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

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

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

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

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

USE [testDataDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_std_scoreList]

	--Excelのマクロ側から渡された引数
	@insertDataFilePath			NVARCHAR(100)	--CSVファイル名(フルパス)

AS
BEGIN

	SET NOCOUNT ON;

	--変数定義
	DECLARE @SQL	NVARCHAR(1000);

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

	BEGIN TRANSACTION
	
	--CSVファイルのデータをインポートする前にテーブルデータを削除しておく
    SET @SQL = N'delete from tbl_score_list'
    EXEC sp_executesql @SQL;

	--テキストファイルをテーブルにインポートする
	SET @SQL = N' BULK INSERT tbl_score_list' +
			   N' FROM ''' + @insertDataFilePath + '''' +		--Excelのマクロ側から渡されたCSVファイル
			   N' WITH' +
			   N' (' +
			   N'  FIELDTERMINATOR  = '',''' +					--データがカンマ区切り
			   N' ,ROWTERMINATOR = ''\n''' +					--終端が改行コード(\n)
			   N'   ,FIRSTROW = 2 ' +							--インポートするデータの開始行
               N' );'
				   
    EXEC sp_executesql @SQL;

	COMMIT TRANSACTION;	

END

コードの解説

注目すべきコード①

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

このコードがBULK INSERTコマンドを実行するためのコードになります。

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

注目すべきコード②

11行目の変数「insertDataFilePath」に、Excelのマクロ側から渡されたパラメタであるCSVファイル名が渡されます。

なお、この変数「insertDataFilePath」の名称は、Excelのマクロ側で記述した「引数」の名前と同じでないといけません。
※Excelのマクロのソースコード32行目を参照

名称が同じでないとCSVファイル名が受け取れないので正しく記述するようにしましょう。

目次へ戻る

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

一つ注意点があるのですが、先ほどの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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、ExcelのマクロからSQL ServerのテーブルにCSVファイルのデータをインポートする方法についてご説明しました。

今回の例では、CSVファイルのデータインポートはBULK INSERTコマンドで行っています。

BULK INSERTコマンドを実行するためのストアドプロシージャを用意し、そのストアドプロシージャにExcelのマクロがインポートするCSVファイル名を渡してBULK INSERTコマンドを実行し、CSVファイルのデータをインポートします。

今回例に挙げたExcelのマクロのコードは少々長いですが、ぜひ参考にしてみてくださいね。

一方、BULK INSERTコマンドを実行するためのストアドプロシージャのコードはあまり長くはないものの、コードが少し分かりにくいかもしれませんが参考にしていただけたら幸いです。

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

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

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

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



目次へ戻る

前の記事

【Excel関数】金種表の作り方について

次の記事

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