Excelのファイルからマクロで.sqlファイルを生成する

よくある「このリストのメンバー全員登録しておいて」という依頼に対応するためのノウハウです。

こんなExcelファイルをDBに登録する必要がある、としましょう。

社員番号	メールアドレス	名前
1	[e-mail]	太郎
2	[e-mail]	次郎
3	[e-mail]	花子

いきなりソースです。
ソースを作ったらExcelのマクロにペーストして実行します。

hoge

Sub createInsertSQLFile()
Dim workSheet As Worksheet
Set workSheet = ThisWorkbook.Worksheets(1)

Dim dataFile As String
dataFile = "hoge.sql"

Open dataFile For Output As #1

Dim i As Long
i = 2
Do While workSheet.Cells(i, 2).Value <> ""
    Print #1, "INSERT INTO shain(email,emproyee_no,name) VALUES('" & workSheet.Cells(i, 2).Value & "','" & workSheet.Cells(i, 1).Value & "','" & workSheet.Cells(i, 3).Value & "');"
    i = i + 1
Loop

Close #1

MsgBox "完了しました。"

End Sub

複数のテーブルに跨る場合

※社員の趣味を格納しておくDBなんて聞いたことありませんが気にしないでください。

社員番号	メールアドレス	名前	趣味
1	[e-mail]	太郎	ゲーム,読書
2	[e-mail]	次郎	ゲーム,食べ歩き
3	[e-mail]	花子	食べ歩き,ダンス

hobbyIdは既にDBに登録済みでそのIDも調査済みであることが前提です。

fuga

Sub createInsertHobbySQLFile()
Dim workSheet As Worksheet
Set workSheet = ThisWorkbook.Worksheets(1)

Dim dataFile As String
dataFile = "fuga.sql"

Open dataFile For Output As #1

Dim i As Long
i = 2
Do While workSheet.Cells(i, 2).Value <> ""
    Dim hobbyNames As Variant
    hobbyNames = Split(workSheet.Cells(i, 4).Value, ",")

    Dim j As Long
    For j = LBound(hobbyNames) To UBound(hobbyNames)
        Dim hobbyId As Long
        If hobbyNames(j) = "ゲーム" Then
            hobbyId = 5
        ElseIf hobbyNames(j) = "読書" Then
            hobbyId = 6
        ElseIf hobbyNames(j) = "食べ歩き" Then
            hobbyId = 7
        ElseIf hobbyNames(j) = "ダンス" Then
            hobbyId = 8
        Else
            hobbyId = 999
        End If

        Print #1, "INSERT INTO hobbies(shain_id, hobby_id) SELECT id, " & CStr(hobbyId) & " FROM shain WHERE email='" & workSheet.Cells(i, 2).Value & "';"
    Next j
    
    i = i + 1
Loop

Close #1

MsgBox "完了しました。"

End Sub

エラーになる場合

• 文字コードを確認。上記のコードだとShift-jisで保存されてしまうため日本語が含まれているとエラーになる場合があります。Atom等でUTF-8に変換して保存しなおせばいけます。

参考

私よりしっかり書いてある例が出ていました・・・
https://utage.headwaters.co.jp/blog/?p=2954