ExcelからSQLのINSERT文を作成するマクロ

エクセルのデータからRDB用のSQLを作成するマクロです。シート名をテーブル名、1行目をテーブルのフィールド名として処理します。

サンプル

作成されるSQL

INSERT INTO bookmark (ID,URL,TITLE) values (0,'http://www.google.co.jp/','GOOGLE');
INSERT INTO bookmark (ID,URL,TITLE) values (1,'http://www.yahoo.co.jp/','Yahoo! JAPAN');

マクロ

Option Explicit

Sub createInsertSql()
    Dim newbook As Workbook
    Dim currentCell As Range
    
    '前処理
    Dim srcSheet As Worksheet
    Set srcSheet = ActiveSheet
    
    Dim targetRange As Range
    Set targetRange = srcSheet.UsedRange


    'INSERT文の前半
    Dim head As String
    head = "INSERT INTO " & srcSheet.Name & " ("
    
    Dim first As Boolean
    first = True
    
    Dim currentColumnIndex As Integer
    For currentColumnIndex = 1 To targetRange.Columns.Count
        If (first) Then
            first = False
        Else
            head = head & ","
        End If
        Set currentCell = srcSheet.Cells(1, currentColumnIndex)
        head = head & currentCell.Value
    Next
    head = head & ") "
    

    '新しいBook作成
    Set newbook = Workbooks.Add
    
    'INSERT文のvalues以降
    Dim currentRowIndex As Integer
    For currentRowIndex = 2 To targetRange.Rows.Count
        
        Dim sql As String
        sql = head & "values ("
        first = True
    
        For currentColumnIndex = 1 To targetRange.Columns.Count
            If (first) Then
                first = False
            Else
                sql = sql & ","
            End If
            Set currentCell = srcSheet.Cells(currentRowIndex, currentColumnIndex)
            If IsNull(currentCell) Or Trim(currentCell.Value) = "" Then
                sql = sql & "null"
            ElseIf IsNumeric(currentCell.Value) Then
                sql = sql & currentCell.Value
            Else
                sql = sql & "'" & currentCell.Value & "'"
            End If
        Next
        
        sql = sql & ");"
        
        newbook.ActiveSheet.Cells(currentRowIndex - 1, 1).Value = sql
    Next
End Sub