この記事では、SQL ServerのテーブルデータをCSVファイルに出力する方法についてご説明します。
【動画】SQL ServerのテーブルデータをCSVファイルに出力する実際の動き
本題に入る前に、まずは次のツイートをご覧ください。
SQL Serverのテーブルデータをテキストファイルに書き出す処理を動画に撮ってみました☺️
区切り文字は好きに指定できますが、動画内ではカンマ「,」を使っています😃#ExcelVBA#SQLServer pic.twitter.com/qh6HlqBJ9s
— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) August 22, 2021
SQL ServerのテーブルデータをExcelのマクロが取得してテキストファイルに出力しています。
動画ではテキストファイルに出力していますが、本記事ではCSVに出力する方法についてご説明します。
SQL ServerのテーブルデータをCSVファイルに出力する方法
SQL ServerのテーブルデータをCSVファイルに出力するには、次の流れの通りにコードを書いていきます。
Excelのマクロ
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのテーブルデータの参照・挿入に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
この列名はSQL Serverのテーブルデータを取得するSELECT文で使います。
(SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、本STEPの処理は必要ありません。)
テーブルデータの文字列を全て一つの文字列に結合させるイメージです。
一つに結合させる理由は、一つの文字列にすることでCSVファイルの書き込みが1回で済むからです。(何度も書き込まないようにする)
※何度も書き込む方式でも間違いではありません。今回は1回の書き込み方式で説明します。
コードの例
Excelのマクロのコード(例)
Dim DBName As String 'データベース名
Dim connDB As String 'データベース接続情報
Dim tblNM As String '取得元のテーブル名
Dim dataStr As String 'SQL Serverから取得したテーブルデータ格納用変数
Dim outputFile As String 'SQL Serverから取得したテーブルデータを書き出すファイル
Dim itm As Variant '配列から取得した値を格納する変数
Dim sqlStr As String 'SQL文
Dim cnt As Long 'カウンタ
Dim dataNum As Long 'データ総数
Dim getVal() As Variant '取得したデータ格納用並列
Dim oCon As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
'カウンタを初期化する
cnt = 1
'DBの名前
DBName = "testDataDB"
'データを取得するテーブル名を取得
tblNM = "tbl_info_list2"
'SQL Serverから取得したテーブルデータを書き出すCSVファイル
outputFile = ActiveWorkbook.Path & "\" & "0063.cav"
'データベース接続情報を取得
connDB = "Provider=SQLNCLI11.1;"
connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
connDB = connDB & "Initial Catalog=" & DBName & ";"
connDB = connDB & "Trusted_Connection=yes;"
'Connectionオブジェクトのインスタンスを生成する
Set oCon = New ADODB.Connection
'SQL Serverに接続する
oCon.Open connDB
'Recordsetオブジェクトのインスタンスを生成する
Set oRS = New ADODB.Recordset
'先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、
'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
'⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
oRS.ActiveConnection = oCon
'テーブルの列名を取得する
sqlStr = "SELECT"
sqlStr = sqlStr & " c.name "
sqlStr = sqlStr & " FROM"
sqlStr = sqlStr & " sys.objects t"
sqlStr = sqlStr & " INNER JOIN sys.columns c ON "
sqlStr = sqlStr & " t.object_id = C.object_id "
sqlStr = sqlStr & " WHERE "
sqlStr = sqlStr & " t.type = 'U'"
sqlStr = sqlStr & " AND t.name='" & tblNM & "' "
sqlStr = sqlStr & " Order BY "
sqlStr = sqlStr & " C.column_id "
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
'レコードセットを閉じる
oRS.Close
'SQL Serverからテーブルデータを取得する
sqlStr = "SELECT "
For Each itm In getVal()
'取得した列名をSELECT文に使う
sqlStr = sqlStr & itm & ","
Next
sqlStr = Left(sqlStr, Len(sqlStr) - 1) '末尾の「'」を削除
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 1 ASC"
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
'レコードセットを閉じる
oRS.Close
'SQL Serverから取得したテーブルデータをdataStrに書き込んでいく
For Each itm In getVal()
If cnt > 1 And (cnt Mod 2) = 0 Then
dataStr = dataStr & itm & vbCrLf
Else
dataStr = dataStr & itm & ","
End If
'カウントを増やす
cnt = cnt + 1
Next
'FileSystemObjectのインスタンス化
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(outputFile)
.Write dataStr 'SQL Serverから取得したテーブルデータをCSVファイルに書き出す
.Close '閉じる
End With
'各終了処理
Set fso = Nothing
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
[/codebox]
コードの解説
注目すべきコード①
最初に見て頂きたいのは108行目から113行目です。
'FileSystemObjectのインスタンス化
Set fso = CreateObject("Scripting.FileSystemObject")
With fso.CreateTextFile(outputFile)
.Write dataStr 'SQL Serverから取得したテーブルデータをCSVファイルに書き出す
.Close '閉じる
End With
111行目のwriteメソッドでSQL Serverから取得したテーブルデータをCSVファイルに書き出しています。
CSVファイルにテーブルデータを書き出したら112行目のcloseメソッドで開いたCSVファイルを閉じます。(保存も完了)
注目すべきコード①
次に見て頂きたいのは95行目から105行目です。
'SQL Serverから取得したテーブルデータをdataStrに書き込んでいく
For Each itm In getVal()
If cnt > 1 And (cnt Mod 2) = 0 Then
dataStr = dataStr & itm & vbCrLf
Else
dataStr = dataStr & itm & ","
End If
'カウントを増やす
cnt = cnt + 1
Next
97行目または99行目でSQL Serverから取得したテーブルデータが入った変数itmの値を結合し、変数dataStrに代入していきます。
97行目は改行文字を文字列の末尾に、99行目はカンマ「,」を文字列の末尾に結合させています。
実際にCSVファイルに出力されるデータ(下のデータの並びを参考に)を見て頂きたいのですが、97行目は1列目のデータの後にカンマ「,」を付け(ex.「data0000001,」)、99行目は1列目と2列目のデータを結合した後のデータ(ex.「data0000001,description0000001」)の後に改行文字「vbCrLf」を結合させてデータを改行させています。
data0000001,description0000001 data0000002,description0000002 data0000003,description0000003 data0000004,description0000004 data0000005,description0000005 data0000006,description0000006 data0000007,description0000007 data0000008,description0000008 data0000009,description0000009 data0000010,description0000010 data0000011,description0000011 data0000012,description0000012 data0000013,description0000013 data0000014,description0000014 data0000015,description0000015 data0000016,description0000016
注目すべきコード③
次に見て頂きたいのは73行目から89行目です。
'SQL Serverからテーブルデータを取得する
sqlStr = "SELECT "
For Each itm In getVal()
'取得した列名をSELECT文に使う
sqlStr = sqlStr & itm & ","
Next
sqlStr = Left(sqlStr, Len(sqlStr) - 1) '末尾の「'」を削除
sqlStr = sqlStr & " FROM " & tblNM
sqlStr = sqlStr & " ORDER BY 1 ASC"
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
73行目から80行目でSQL Serverのテーブルデータを取得するSELECT文を用意し、89行目でrecordsetのGetRowsメソッドでSQL Serverからテーブルデータを取得しています。
注目すべきコード④
次に見て頂きたいのは37行目です。
'SQL Serverに接続する
oCon.Open connDB
ConnectionオブジェクトのOpenメソッドを使ってSQL Serverに接続しています。
補足
本記事では、取得するSQL Serverテーブルデータのテーブルの列名を取得しています。
'テーブルの列名を取得する
sqlStr = "SELECT"
sqlStr = sqlStr & " c.name "
sqlStr = sqlStr & " FROM"
sqlStr = sqlStr & " sys.objects t"
sqlStr = sqlStr & " INNER JOIN sys.columns c ON "
sqlStr = sqlStr & " t.object_id = C.object_id "
sqlStr = sqlStr & " WHERE "
sqlStr = sqlStr & " t.type = 'U'"
sqlStr = sqlStr & " AND t.name='" & tblNM & "' "
sqlStr = sqlStr & " Order BY "
sqlStr = sqlStr & " C.column_id "
'データを抽出するSQL文をSourceプロパティに設定する
oRS.Source = sqlStr
'指定したテーブルのデータを参照する(アクセスする)
oRS.Open
'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納)
getVal = oRS.GetRows
この列名はSQL Serverのデータを取得するSELECT文で使います。
ただし、SELECT文に直接列名を指定したり、列名は使わずに「SELECT *」でテーブルデータを取得する場合は、列名の取得は不要です。
もしテーブルの列名を使いたい場合は次にお見せする、列名を取得するSELECT文を参考にしてください。
SELECT c.name FROM sys.objects t INNER JOIN sys.columns c ON t.object_id = C.object_id WHERE t.type = 'U' AND t.name = 'tbl_info_list2' Order BY C.column_id
8行目でテーブル名を指定しています。お使いのテーブル名に置き換えて参考にしてくださいね。
ちなみに上記SELECT文のテーブルの定義と実際にSELECT文を実行した結果は次の画像の通りです。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの11行目の「ADODB.Connection」と12行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection 'Connection用変数
Dim oRS As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、ExcelのマクロからSQL ServerのテーブルデータをCSVファイルに出力する方法についてご説明しました。
ザックリとした処理の流れとしては、
- ①CSVファイルに書き出したいテーブルデータを抽出するSQL文を記述
- ②SQL Serverに接続
- ③ ①のSQL文の条件を満たしたテーブルデータを抽出し、その抽出データをレコードセットから取得
- ④ ③のデータの文字列を結合させて変数に格納
- ⑤ ④の変数の値をCSVファイルに書き込む
以上になります。
SQL ServerのテーブルデータをCSVファイルに出力したい時に本記事を参考にしていただけたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
