ExcelからINSERT文を生成するアドインを作ってみた

はじめに

試験工程などで、データ準備のために大量のINSERT文を作成する必要があるかと思います。

そんな時に、INSERT文を簡単に作成できると便利だと思い、ExcelのデータからINSERT文を生成できる独自の関数を作成してみました。

概要

• VBAでINSERT文を生成する関数
• 作成した関数をアドイン化し、Excel上で=作成した関数名（引数）の形式で実行できるようにする
• DBMSはpostgresqlに対応
• 値に関してはデータ型に応じて、'(シングルクォーテーション)をつけるなり、適切にキャストさせる

使い方

セル上に=PG_CREATEINSERTと記載し、引数に以下を指定します。

引数指定例

 =PG_CREATEINSERT（空白を無視するかどうか(true or false), テーブル名, カラム名, 値, データ型）
 =PG_CREATEINSERT（空白を無視するかどうか(true or false), テーブル名, カラム名, 値）

実行例

作成したPG_CREATEINSERT関数の実行例は以下です。

実行結果

実行結果を見てもわかる通り、セルの値からINSERT文が作成できていることを確認できました。
指定したデータ型に応じて、値の出力形式が変動します。

データ型	出力形式
文字型	セルの値をシングルクォーテーションで囲い出力
bytea型	'\x + セルの値 + '::byteaの形式で出力
上記以外	セルの値をそのまま出力

ソースコード

pg_createInsert.xlam

'*********************************************************
'[概要]
' 指定したセルの値をもとに、postgresqlに対応したINSERT文を出力します
'
'[使い方]
' =PG_CREATEINSERT（空白を無視するかどうか, テーブル名, カラム名, 値, データ型）
' =PG_CREATEINSERT（空白を無視するかどうか, テーブル名, カラム名, 値）
'
'[引数]
' Ignore：空白を無視するかどうか
' TableName：テーブル名
' par()：可変長引数（カラム名, 値, データ型の順番で指定される想定）
'*********************************************************
Function PG_CREATEINSERT(Ignore As Boolean, TableName As String, ParamArray par() As Variant)
   
    Dim rowVal As String: rowVal = ""
    Dim setVal As String: setVal = ""
    Dim colmnName As String: colmnName = ""
    Dim SetValues As String: SetValues = ""
   
    Dim colCount As Integer: colCount = 0
    Dim valCount As Integer: valCount = 0
    Dim parCount As Integer: parCount = UBound(par) + 1 '可変長引数の数
   
    Dim colmnArr() As String
    Dim typeArr() As String
    Dim valueArr() As String
   
    Const Delim As String = ","
    PG_CREATEINSERT = "" '戻り値

   
    '引数チェック
    If parCount <= 1 Or parCount >= 4 Then
        PG_CREATEINSERT = CVErr(xIErrNum)
        Exit Function
    End If
   
    'カラム名設定
    colmnArr() = setParam(par(0))
    '値設定
    valueArr() = setParam(par(1))
    'データ型設定
    If parCount = 3 Then
        typeArr() = setParam(par(2))
    End If
   
    'INSERT文作成
    'カラム名
    For i = LBound(colmnArr) To UBound(colmnArr)
        If colmnArr(i) <> "" Or Ignore = False Then
            colmnName = colmnName & Delim & colmnArr(i)
            colCount = colCount + 1
        End If
    Next
   
    '値
    For i = LBound(valueArr) To UBound(valueArr)
        If valueArr(i) <> "" Or Ignore = False Then
            rowVal = Replace(valueArr(i), """", "")
            If Not (LCase(rowVal) = "null" Or rowVal = "") Then
                If parCount = 3 Then
                    'データ型が存在する場合、データ型に合わせて値を設定する
                    If typeArr(i) Like "*char*" Or LCase(typeArr(i)) = "text" Then
                        '文字列型
                        setVal = "'" & rowVal & "'"
                    ElseIf LCase(typeArr(i)) = "bytea" Then
                        'bytea型
                        setVal = "'\x" & rowVal & "'" & "::bytea"
                    Else
                        '上記以外（数値型など）
                        setVal = rowVal
                    End If
                Else
                    setVal = "'" & rowVal & "'"
                End If
            Else
                setVal = "NULL"
            End If
            SetValues = SetValues & Delim & setVal
            valCount = valCount + 1
        End If
    Next
   
    If colCount <> valCount Then
        PG_CREATEINSERT = CVErr(xIErrNum)
        Exit Function
    End If
   
    'INSERT文生成
    PG_CREATEINSERT = "INSERT INTO " & TableName _
                    & "(" & Mid(colmnName, Len(Delim) + 1) & ")" _
                    & " VALUES " _
                    & "(" & Mid(SetValues, Len(Delim) + 1) & ")" & ";"

End Function

'*********************************************************
'[概要]
' 可変長引数の各要素を配列に設定して返却する関数
'*********************************************************
Function setParam(paramRange As Variant)
    Dim rtnArr() As String
    If TypeName(paramRange) = "Range" Then
        ReDim rtnArr(paramRange.Columns.Count - 1)
        For i = 0 To UBound(rtnArr)
            rtnArr(i) = paramRange(1, i + 1).Value
        Next
        setParam = rtnArr()
    Else
        ReDim rtnArr(0)
        rtnArr(0) = CStr(paramRange)
        setParam = rtnArr()
    End If

End Function

アドイン設定方法

Excel上で今回作成した関数を=作成した関数名（引数）の形式で利用するためには、アドインを作成して登録する必要があります。

手順に関しては【EXCEL VBA】自作のユーザー定義関数をアドイン化する アドイン作成が参考になりますので、そちらを参照してください。