目的

Excelを使って、大量のデータからSQLのINSERT文を効率的に作成します。
これまでExcelのセル関数を使って作成していましたが、汎用性を高めるために簡単なマクロを用意しました。

作成手順

1.マクロ有効ブック（*.xlsmファイル）を作成

通常のExcelファイル（*.xlsx）ではマクロを保存できないため、マクロ有効形式で作成します。

2.シートの準備

以下の3つのシートを作成します。
1. 「設定」シート
  ⇒ テーブル情報やデータを記載するシート
2. 「INSERT出力」シート
  ⇒ マクロ実行時にINSERT文が出力されるシート（白紙でOK）
3. マクロ実行ボタン用のシート（任意）
  ⇒ ボタンを配置する場合に利用します。ボタンがなくてもマクロダイアログから実行可能

3.「設定」シートの構成

セル位置	内容
B1	テーブル名を入力
C1:H1	シングルクォーテーション（'）を省略するカラム名を入力（省略しない場合は空白）
2行目	データのカラム名を記載
3行目以降	登録するデータを入力

↓こんなイメージ

4.マクロ実行ボタンを作成

Visual Basic for Applications（VBA）を使用して、以下のコードを登録します。

コード

Sub CreateInsertSQL()
    Dim wsConfig As Worksheet
    Dim wsOutput As Worksheet
    Dim tableName As String
    Dim columnNames As Range
    Dim dataRange As Range
    Dim insertSQL As String
    Dim row As Range
    Dim col As Range
    Dim cell As Range
    Dim value As String
    Dim sqlStartRow As Integer
    Dim lastColumn As Integer
    Dim lastRow As Long
    Dim rowHasData As Boolean
    Dim noQuoteColumns As Range
    Dim noQuoteColumn As Range
    Dim matchFound As Boolean

    ' シートの設定
    Set wsConfig = ThisWorkbook.Sheets("設定")
    Set wsOutput = ThisWorkbook.Sheets("INSERT出力")
    
    ' INSERT出力シートの内容を全消去
    wsOutput.Cells.Clear
    
    ' テーブル名の取得
    tableName = wsConfig.Cells(1, 2).value
    
    ' 2行目のA列から最右列までのカラム名を設定
    lastColumn = wsConfig.Cells(2, wsConfig.Columns.Count).End(xlToLeft).Column
    Set columnNames = wsConfig.Range(wsConfig.Cells(2, 1), wsConfig.Cells(2, lastColumn))
    
    ' D1からH1までのカラム名に対応するシングルクォーテーションを省くカラムを設定
    Set noQuoteColumns = wsConfig.Range("D1:H1") ' D1～H1のカラム名
    
    ' 最後のデータが入っている行を取得
    lastRow = wsConfig.Cells(wsConfig.Rows.Count, 1).End(xlUp).row
    
    ' 3行目から最終行までのデータ範囲を設定
    Set dataRange = wsConfig.Range(wsConfig.Cells(3, 1), wsConfig.Cells(lastRow, lastColumn))
    
    ' INSERT文の出力開始位置
    sqlStartRow = 1
    
    ' INSERT文の作成
    For Each row In dataRange.Rows
        rowHasData = False
        
        ' 行にデータがあるか確認
        For Each cell In row.Cells
            If cell.value <> "" Then
                rowHasData = True
                Exit For
            End If
        Next cell
        
        ' 行にデータがあればINSERT文を作成
        If rowHasData Then
            insertSQL = "INSERT INTO " & tableName & " (" ' INSERT INTO <テーブル名> (カラム名)
            
            ' カラム名を追加
            For Each col In columnNames.Columns
                ' 空白のカラム名はスキップ
                If col.value <> "" Then
                    insertSQL = insertSQL & col.value & ", "
                End If
            Next col
            
            ' 最後のカンマを削除
            insertSQL = Left(insertSQL, Len(insertSQL) - 2) & ") VALUES ("
            
            ' 行のデータを追加
            For Each cell In row.Cells
                value = cell.value
                matchFound = False
                
                ' D1～H1のカラム名に一致する場合はシングルクォーテーションを省く
                For Each noQuoteColumn In noQuoteColumns
                    If noQuoteColumn.value = columnNames.Cells(1, cell.Column).value Then
                        matchFound = True
                        Exit For
                    End If
                Next noQuoteColumn
                
                ' 一致した場合、シングルクォーテーションを省く
                If matchFound Then
                    If value = "" Then
                        insertSQL = insertSQL & "NULL, "
                    Else
                        insertSQL = insertSQL & value & ", " ' シングルクォーテーションなし
                    End If
                Else
                    ' 一致しなければシングルクォーテーションを追加
                    If value = "" Then
                        insertSQL = insertSQL & "NULL, "
                    Else
                        insertSQL = insertSQL & "'" & value & "', " ' シングルクォーテーションあり
                    End If
                End If
            Next cell
            
            ' 最後のカンマを削除してVALUESの閉じ括弧を追加
            insertSQL = Left(insertSQL, Len(insertSQL) - 2) & ");"
            
            ' 出力シートにINSERT文を記入
            wsOutput.Cells(sqlStartRow, 1).value = insertSQL
            sqlStartRow = sqlStartRow + 1 ' 次の行に移動
        End If
    Next row
    
    MsgBox "INSERT文が作成されました！"
End Sub

5.マクロ実行ボタンの設置（任意）

開発タブ > 挿入 > ボタンを選択。
マクロ実行ボタンをシート上に配置。
ボタンに上記マクロを紐づけます。

終わり

以上の手順で、大量のテーブルデータを手軽にINSERT文へ変換できます。
VBA初心者でもカスタマイズ可能です。