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

こんにちは！ 健史(たけふみ)です。

オラクルやＳＱＬサーバなどのデータベースを使われている方で
ＳＱＬ文として「ExcelデータからのINSERT文を作成したい」
と思ったことありませんか。

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

尚この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。

Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。

Excel-VBA 実例 この通りやれば動く、とにかく触ってみよう

こんにちは！ 健史(たけふみ)です。 パソコンをお使いの方の多くは、表計算ソフトExcelを利用されていると思います。 この記事は、Exceは知っているけれど ・Excel-VBAって何？ ・Excel-VBAは取っ付きにく...

jibun-kaizen.com

2020.09.20

実行中に発生したエラーの対処法は、上記記事の2.エラーが発生したときの対処法に記載しています。(この段落のリンクから直接遷移します)

プログラムの作成と実行

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

です。

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

Excelデータとして持っているのもから別のフォーマットに変換したり、項目の前後に意味のある文言を追加した内容に変換しなければならないなどの場合に応用して頂ければと思っています。


プログラムの概要は以下の通りです。

◇Excelデータ

顧客番号　名前　　　生年月日
01000001　改善太郎　20010101

から

◇INSERT文を生成する

INSERT INTO HANBAIDB.顧客Ｍ (顧客番号,名前,生年月日) VALUES('01000001','改善太郎',20010101)

Excelで対応する場合は、
・空白の列に以下を入力

="INSERT INTO HANBAIDB.顧客Ｍ (" & $A$1 & "," & $B$1 & "," & $C$1 & ") VALUES('" & A2 & "',"　& B2 & "'," & C2 & ")"

・データが入っている行数分をコピー
・上記で生成されたSQL全てをコピー
・別の列に[形式][値]で貼り付け
といった対応でしょうか。

項目数が100も200もあったら手間の掛かる作業で、かつ、間違えやすかったり面倒です。

そんな時に対応するプログラムです。

実行までの手順

1.Excelを起動し、データ準備
1).Excel2007以降は、Sheet1のみであるためシートを１つ追加し、全部で２つ作成しておきます。
2).シート１には、予め作成しておいた追加データを格納しておきます。

上記の説明と異なる箇所があります。

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

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

"上記説明と異なる箇所"というのは、
・上記では[VALUES('" & A2 & "',"　& B2 & "'," & C2 & ")"]ですが
・この項の実データは３行目からになるので、説明を合わせるのであれば[VALUES('" & A3 & "',"　& B3 & "'," & C3& ")"]
ということです。

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

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

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

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

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

            If j1 = max_col1 Then
                strSql = strSql & ");"
            Else
                strSql = strSql & ","
            End If
        Next
'シート2に生成されたSQL文を格納
        i2 = i2 + 1
        Worksheets(2).Cells(i2, 1) = strSql
    Next

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

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

7.F5キーで実行

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

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

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

フローチャート

[For～Next]の部分だけですが、以下の記事のフローチャートを参照下さい。

Excel-VBA 実例 シートの内容を比較して異なる部分を出力

こんにちは！ 健史(たけふみ)です。 Excelシートで、 「レイアウトも格納されているデータの順番も同じ２つのシートから、内容の違っているセルを探したい！」 ってことありませんか。 行数が1万行とか、あまりにも多い...

jibun-kaizen.com

-0001.11.30

変数名と処理回数が異なりますが、処理形式[For～Next]の中に[For～Next]は同じです。

今回は[For～Next]の中に[For～Next]さらにもう1回[For～Next]がある形です。

プログラムの詳細説明

上記フロー同様、プログラムの説明も以下の記事を参照下さい。

Excel-VBA 実例 シートの内容を比較して異なる部分を出力

こんにちは！ 健史(たけふみ)です。 Excelシートで、 「レイアウトも格納されているデータの順番も同じ２つのシートから、内容の違っているセルを探したい！」 ってことありませんか。 行数が1万行とか、あまりにも多い...

jibun-kaizen.com

-0001.11.30

補足です。

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

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

Select Case Worksheets(1).Cells(2, j1)～End Selectの部分です。

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

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

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

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

        strSql = "INSERT INTO HANBAIDB.顧客Ｍ (" & vbCrLf

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

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

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

Sub sql_insert2()
    Dim i1, j1 As Long
    Dim max_row1, max_col1 As Long
    Dim strSql As String
'テキストファイルに出力するためのファイルパスを定義
    Dim dataFile As String
'テキストファイルの出力先をセット
    dataFile = "C:\temp\sql.txt"

    max_row1 = Worksheets(1).Range("A3").End(xlDown).Row

    max_col1 = Worksheets(1).Range("A1").End(xlToRight).Column
'テキストファイルを開く
    Open dataFile For Output As #1
    
    For i1 = 3 To max_row1
        strSql = "INSERT INTO HANBAIDB.顧客Ｍ ("
        
        For j1 = 1 To max_col1
            strSql = strSql & Worksheets(1).Cells(1, j1)
            If j1 = max_col1 Then
                strSql = strSql & ") VALUES ("
           Else
                strSql = strSql & ","
           End If
        Next
        
        For j1 = 1 To max_col1
            If Worksheets(1).Cells(i1, j1) = "" Then
                strSql = strSql & "null"
            Else
                Select Case Worksheets(1).Cells(2, j1)
                Case ""
                    strSql = strSql & "'" & Worksheets(1).Cells(i1, j1) & "'"
                Case "N"
                    strSql = strSql & Worksheets(1).Cells(i1, j1)
                End Select
            End If
            If j1 = max_col1 Then
                strSql = strSql & ");"
            Else
                strSql = strSql & ","
            End If
        Next
'テキストファイルに書く
        Print #1, strSql
        Print #1, ""
    Next
'テキストファイルを閉じる
    Close #1

テキストファイルの出力先は、プログラム内コメント[テキストファイルの出力先をセット]直下の１行を変更します。

    dataFile = "C:\temp\sql.txt"

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

        Print #1, ""

最後に

冒頭で書いた通り、項目数が多い場合にExcel関数で実現しようとすると手間が掛かります。

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