ExcelデータをINSERT文に変換する

DBに入れるデータをExcelで管理しているときに使ってるやつ。

使用例

シートにこんな感じで書かれていた場合（シート名はHOGE）、

	A	B	C
1	COL1	COL2	COL3
2	data	1
3	data	(null)	null
4	data	1	data

SQLはこうなります。

INSERT ALL
INTO HOGE (COL1,COL2,COL3) values('data',1,null)
INTO HOGE (COL1,COL2,COL3) values('data',null,null)
INTO HOGE (COL1,COL2,COL3) values('data',1,'data')
SELECT * FROM dual;

使い方

Excelシートの仕様はこう。

• 1行目:カラム名
• 2行目～:データ
• シート名:テーブル名

シート上にボタンを置いて、マクロ登録。以下の関数を実行。

ExcelデータをINSERT文に変換する

Option Explicit

Sub INSERT文を生成_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim sql As String
    sql = "INSERT ALL"
    
    Dim head As String
    head = vbLf & "INTO " & ws.Name & " ("
    
    Dim target As Range
    Set target = ws.UsedRange
    
    Dim currentCell As Range
    Dim i As Integer
    
    ' カラム名を列挙
    For i = 1 To target.Columns.Count
        If (i <> 1) Then
            head = head & ","
        End If
        Set currentCell = ws.Cells(1, i)
        head = head & currentCell.Value
    Next
    head = head & ") values("
    
    ' 値を列挙
    Dim j As Integer
    Dim strtmp As String
    For j = 2 To target.Rows.Count
        
        ' 非表示の行は出力しない
        If Rows(j).Hidden Then GoTo Next_RowLoop
        
        sql = sql & head
        
        For i = 1 To target.Columns.Count
            If (i <> 1) Then
                sql = sql & ","
            End If
            Set currentCell = ws.Cells(j, i)
            If (IsNull(currentCell) Or currentCell.Value = "" Or Trim(currentCell.Value) = "(null)" Or Trim(currentCell.Value) = "null") Then
            
                ' null
                sql = sql & "null"
            ElseIf IsNumeric(currentCell.Value) Then
            
                ' 数値
                sql = sql & currentCell.Value
            ElseIf Left(currentCell.Value, 8) = "(SELECT " Then
            
                ' SQLっぽいものはそのまま
                sql = sql & currentCell.Value
            Else
            
                ' 文字列はシングルクォーテーションをエスケープする
                strtmp = Replace(currentCell.Value, "'", "''")
                
                ' セル内の改行を改行コードに変換
                strtmp = Replace(strtmp, vbLf, "' || CHR(13) || CHR(10) ||'")
                
                sql = sql & "'" & strtmp & "'"
            End If
        Next
        
        sql = sql & ")"
        
Next_RowLoop:
    Next
    
    sql = sql & vbLf & "SELECT * FROM dual;"
    
    Dim cb As New DataObject
    With cb
        .SetText sql
        .PutInClipboard
    End With
    MsgBox ("クリップボードにコピーしました")
End Sub

変換したINSERT文はクリップボードにコピーします。
そのためにDataObjectを使うので、適当なActiveXコンポーネントをシート上に置いておきます。一度置いたらあとは消していいです。

注意

列数と行数が多いとSQL流したときにエラーが出ます。
適当にデータを区切ってINSERT文を作れば大丈夫です。