大量のテーブルデータを簡単にINSERT文にする方法【Excelマクロ編】

目的

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.マクロ実行ボタンの設置（任意）

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

終わり

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