Looking for Function to Format Values for SQL INSERT Statement

[Alex20850]

I have values in Excel spreadsheets that I would like to format for creating INSERT statements to be run in SQL.

Based on whether a column heading value is C for Character or N for numeric, the value would either apostrophe before and after the value or no apostrophes if it is numeric.

[Sorry, don't know how to paste image.]

I am looking for function - CREATEINSERT for example - that would create the values inside the individual
values inside the parenthesis based on the values in row 6 which has C and N.
=CREATEINSERT($B6,B8) would produce '01001' while
=CREATEINSERT($E6,E8) would produce '55347'.
	C	C	C	N	N
COUNTIES	COUNTYFIPS	STATEABBR	COUNTYNAME	POP2015	AREASQMI
INSERT INTO COUNTIES VALUES ('01001','AL','Autauga County',55347,594.436);	01001	AL	Autauga County	55347	594.436
INSERT INTO COUNTIES VALUES ('01003','AL','Baldwin County',203709,1589.784);	01003	AL	Baldwin County	203709	1589.784
INSERT INTO COUNTIES VALUES ('01005','AL','Barbour County',26489,884.876);	01005	AL	Barbour County	26489	884.876
INSERT INTO COUNTIES VALUES ('01007','AL','Bibb County',22583,622.582);	01007	AL	Bibb County	22583	622.582
INSERT INTO COUNTIES VALUES ('01009','AL','Blount County',57673,644.776);	01009	AL	Blount County	57673	644.776
INSERT INTO COUNTIES VALUES ('01011','AL','Bullock County',10696,622.805);	01011	AL	Bullock County	10,696	622.805

<tbody>
</tbody>

 

[lrobbo314]

This UDF seems to do what you're looking for.

Function CREATEINSERT(r As Range, head As Range, table As String) As String
Dim ar() As Variant: ar = r.Value
Dim h() As Variant: h = head.Value
Dim res As String: res = "INSERT INTO " & table & " VALUES ("


If UBound(ar, 2) = UBound(h, 2) Then
    For i = LBound(ar) To UBound(ar, 2)
        If (h(1, i)) = "N" Then
            res = res & ar(1, i) & ","
        Else
            If i = 1 Then
                res = res & "'" & Format(ar(1, i), "00000") & "',"
            Else
                res = res & "'" & ar(1, i) & "',"
            End If
        End If
    Next i
End If


CREATEINSERT = Left(res, Len(res) - 1) & ");"
            
End Function

Book1
	A	B	C	D	E	F
1		C	C	C	N	N
2	Formulas	COUNTYFIPS	STATEABBR	COUNTYNAME	POP2015	AREASQMI
3	INSERT INTO COUNTIES VALUES ('01001','AL','Autauga County',55347,594.436);	1001	AL	Autauga County	55347	594.436
4	INSERT INTO COUNTIES VALUES ('01003','AL','Baldwin County',203709,1589.784);	1003	AL	Baldwin County	203709	1589.784
5	INSERT INTO COUNTIES VALUES ('01005','AL','Barbour County',26489,884.876);	1005	AL	Barbour County	26489	884.876
6	INSERT INTO COUNTIES VALUES ('01007','AL','Bibb County',22583,622.582);	1007	AL	Bibb County	22583	622.582
7	INSERT INTO COUNTIES VALUES ('01009','AL','Blount County',57673,644.776);	1009	AL	Blount County	57673	644.776
8	INSERT INTO COUNTIES VALUES ('01011','AL','Bullock County',10696,622.805);	1011	AL	Bullock County	10,696	622.805
Sheet1

Cell Formulas
Range	Formula
A3	=CREATEINSERT(B3:F3,$B$1:$F$1,"COUNTIES")
A4	=CREATEINSERT(B4:F4,$B$1:$F$1,"COUNTIES")
A5	=CREATEINSERT(B5:F5,$B$1:$F$1,"COUNTIES")
A6	=CREATEINSERT(B6:F6,$B$1:$F$1,"COUNTIES")
A7	=CREATEINSERT(B7:F7,$B$1:$F$1,"COUNTIES")
A8	=CREATEINSERT(B8:F8,$B$1:$F$1,"COUNTIES")

 

[lrobbo314]

Refactored the code a bit to clean it up.

Function CREATEINSERT(r As Range, head As Range, table As String) As String
Dim ar() As Variant:    ar = r.Value
Dim h() As Variant:     h = head.Value
Dim res As String:      res = "INSERT INTO " & table & " VALUES ("
Dim val As Variant:     val = vbNullString
Dim cn As String:       cn = vbNullString


If UBound(ar, 2) = UBound(h, 2) Then
    For i = LBound(ar) To UBound(ar, 2)
        cn = h(1, i)
        val = ar(1, i)
        If i = 1 Then val = Format(val, "00000")
        If cn = "C" Then val = "'" & val & "'"
        res = res & val & ","
    Next i
End If


CREATEINSERT = Left(res, Len(res) - 1) & ");"
            
End Function

 

[Alex20850]

Wow!! Works great. Thank you so much. Very useful and flexible.
Really appreciate.

 

[Alex20850]

This works great, but I have found something that would be a nice tweak.
<code>INSERT INTO COUNTIES VALUES ('24035','MD','Queen Anne's County',48904,371.908); returns the error ORA-00917: missing comma
It needs the apostrophe to be doubled to avoid the error.
INSERT INTO COUNTIES VALUES ('24035','MD','Queen Anne''s County',48904,371.908);
Could the function fix that?


</code>

 

[lrobbo314]

Give this a shot.

Function CREATEINSERT(r As Range, head As Range, table As String) As String
Dim ar() As Variant:    ar = r.Value
Dim h() As Variant:     h = head.Value
Dim res As String:      res = "INSERT INTO " & table & " VALUES ("
Dim val As Variant:     val = vbNullString
Dim cn As String:       cn = vbNullString




If UBound(ar, 2) = UBound(h, 2) Then
    For i = LBound(ar) To UBound(ar, 2)
        cn = h(1, i)
[COLOR=#0000cd]        val = Replace(ar(1, i), "'", "''")[/COLOR]
        If i = 1 Then val = Format(val, "00000")
        If cn = "C" Then val = "'" & val & "'"
        res = res & val & ","
    Next i
End If




CREATEINSERT = Left(res, Len(res) - 1) & ");"
            
End Function

 

[Alex20850]

Works great. Thank you very much. This has been very useful for me.
May I submit your work to SQL groups?