Excel-VBA 実例 ExcelデータからSQL文を自動生成

こんにちは！ 健史です。

オラクルやＳＱＬサーバなどのデータベースを使われている方で

と思ったことありませんか。

空白の列に'="INSERT INTO ～"を埋め込むことでもできますが、Excel-VBAで作成してみました。

尚、プログラムは基本的なコマンドのみを使うようにして、読みやすく理解しやすいようにしています。

プログラムの作成と実行

今回のサンプルプログラムは

です。

実行までの手順

1.Excelを起動し、データ準備

1).Excel2007以降は、Sheet1のみであるためシートを１つ追加し、全部で２つ作成しておきます。

2).シート１には、予め作成しておいたり、すでにあるデータを加工編集したデータを格納しておきます。

見出し行とデータ行の間に１行追加し、数字タイプの場合は追加した２行目に[N]を入力しています。

項目が数字タイプの場合場合には、囲い込み文字である['](シングルクォーテション)は不要にするためです。(後述)

2.Excel-VBAを起動
Altを押した状態でF8キーを押します。

3.[マクロ名(M):]に'test'と入力(''は不要、''内のtestを入力)

4.[作成(C)]をクリック
私はマクロ名を入力したら、そのままEnterを押します。

5.以下のプログラムをドラッグ＆コピー

'テーブル名を定義
    Const TABLENAME = "HANBAIDB.顧客Ｍ"
'変数定義
    Dim ix1, ix2, iy1 As Long
    Dim ix1_max, iy1_max As Long
    Dim strSql As String
'１列3行目を起点にして最後の行を求める。サンプルでは[6]
    ix1_max = Sheets(1).Range("A3").End(xlDown).Row
'１列1行目を起点にして最後の列を求める。サンプルでは[5]
    iy1_max = Sheets(1).Range("A1").End(xlToRight).Column
'シート2をクリア
    Sheets(2).Cells.Clear
'シート2に生成されたSQL文を格納するための添字に初期値[2]をセットしておく
    ix2 = 2
'SQL文を生成
    For ix1 = 3 To ix1_max
        strSql = "INSERT INTO " & TABLENAME & " ("
'項目名をセット
        For iy1 = 1 To iy1_max
            strSql = strSql & Sheets(1).Cells(1, iy1)
            If iy1 = iy1_max Then
                strSql = strSql & ") VALUES ("
           Else
                strSql = strSql & ","
           End If
        Next
'データをセット
        For iy1 = 1 To iy1_max
            Select Case Sheets(1).Cells(ix1, iy1)
            Case ""
                strSql = strSql & "NULL"
            Case Else
                Select Case Sheets(1).Cells(2, iy1)
                Case ""
                    strSql = strSql & "'" & Sheets(1).Cells(ix1, iy1) & "'"
                Case "N"
                    strSql = strSql & Sheets(1).Cells(ix1, iy1)
                End Select
            End Select
            If iy1 = iy1_max Then
                strSql = strSql & ");"
            Else
                strSql = strSql & ","
            End If
        Next
'シート2に生成されたSQL文を格納
        ix2 = ix2 + 1
        Sheets(2).Cells(ix2, 1) = strSql
    Next

6.以下に貼り付け(ペースト)

Sub test()
ここに貼り付ける
End Sub

7.F5キーで実行

8.実行結果の確認
もとのデータファイルに切り替えて下さい。
Altを押した状態でTabキーを押して、Book1に位置づいたらTabキーを離すことで切り替わります。

シート２には、INSERTのSQL文が生成されています。

シート２のSQL文は1行目からではなく、シート１と同じ3行目から生成しました。

補足説明

・テーブル名は先頭にある[TABLENAME]の設定値を修正する
テーブル名は、[strSql = "INSERT INTO " & TABLENAME & " ("]でセットいます。

上記の箇所に'HANBAIDB.顧客Ｍ'をコーディングしても同じですが、処理の中にあります。

別のテーブルに使用する場合は、先頭で定義しておくほうが変更しやすいです。

・シート１の２行目に挿入した意味
シート１で項目[生年月日]は、数字タイプとして扱っています。

そのため、追加した２行目には[N]と入力し、プログラムで[N]の場合には['](シングルクォーテション)を付けないようにしています。

言うまでもなく、特に[N]でなくても何でも良いんですよ。

[Num]と入力したら、プログラム内の判断部分も合わせれば良いだけです。

・見栄えや出来上がりは任意に修正する
例えば「スペースの個数が少なく見ずらい！」とか「改行を入れたい！」などの場合には、修正します。

適度に改行を入れたい場合には、以下のように[ & vbCrLf]を追記します。

        strSql = "INSERT INTO " & TABLENAME & " (" & vbCrLf

また、「最後の[;]は要らない！」場合には、削除します。

生成した文字列をファイルに出力する場合

SQL文をExcel内ではなくテキストファイルに出力する場合には、以下の通りです。

'テーブル名を定義
    Const TABLENAME = "HANBAIDB.顧客Ｍ"
'ファイル名を定義
    Const FILENAME = "C:\temp\sql.txt"
'変数定義
    Dim ix1, iy1 As Long
    Dim ix1_max, iy1_max As Long
    Dim strSql As String
'テキストファイルを開く
    Open FILENAME For Output As #1
'１列3行目を起点にして最後の行を求める。サンプルでは[6]
    ix1_max = Sheets(1).Range("A3").End(xlDown).Row
'１列1行目を起点にして最後の列を求める。サンプルでは[5]
    iy1_max = Sheets(1).Range("A1").End(xlToRight).Column
'SQL文を生成
    For ix1 = 3 To ix1_max
        strSql = "INSERT INTO " & TABLENAME & " ("
'項目名をセット
        For iy1 = 1 To iy1_max
            strSql = strSql & Sheets(1).Cells(1, iy1)
            If iy1 = iy1_max Then
                strSql = strSql & ") VALUES ("
           Else
                strSql = strSql & ","
           End If
        Next
'データをセット
        For iy1 = 1 To iy1_max
            Select Case Sheets(1).Cells(ix1, iy1)
            Case ""
                strSql = strSql & "NULL"
            Case Else
                Select Case Sheets(1).Cells(2, iy1)
                Case ""
                    strSql = strSql & "'" & Sheets(1).Cells(ix1, iy1) & "'"
                Case "N"
                    strSql = strSql & Sheets(1).Cells(ix1, iy1)
                End Select
            End Select
            If iy1 = iy1_max Then
                strSql = strSql & ");"
            Else
                strSql = strSql & ","
            End If
        Next
'テキストファイルに書く
        Print #1, strSql
        Print #1, ""
    Next
'テキストファイルを閉じる
    Close #1

テキストファイルの出力先も先頭の[FILENAME]の設定値を変更します。

    Const FILENAME = "C:\temp\sql.txt"

また、テキストファイルに出力する際に１行空白行を挿入していますが、不要な場合には以下を削除します。

        Print #1, ""

最後に

テーブル名やＳＱＬをファイルに出力する場合のフィル名について、設定用のシートを追加することも考えたのですが、「手っ取り早くＳＱＬを生成したい場合が多いのではないのかなぁ」と考ました。

そのため今回は、プログラム内で定義する方法としました。

修正する手間は同じですが「シートを移動して修正」よりは「データシートはセットした、あとはプログラムを実行する際に修正」の方が。


今回のサンプルは、SQLという専門性の偏ったものを生成する内容になりましたが、SQLにこだわることはありません。

項目数が多い場合にExcel関数で実現しようとすると手間が掛かりますし、ミスも起きやすいでしょう。

プログラムであれば、データをプログラムで処理するルール通りに作成して置けば、直ぐに適用できます。