How to CONCATENATE >50 fields in Excel table into SQL Insert State

[will~]

Excel 2003 SP3 on Windows XP SP2

I have problem when trying to construct an SQL Insert statement using the
CONCATENATE function in Excel. There are over 50 fields.

I would like to construct an SQL insert statement from tables stored in
Excel to be used to populate tables in a SQL database. Using the CONCATENATE
function below I ends up with a complete SQL insert statement

=CONCATENATE("INSERT INTO Product (Field1,Field2,Field3,Field4,Field5)
VALUES ('",B2,"','",C2,"',",D2,",",E2,",",F2,")")

When I try to do the same for a large table (> 50 fields). I have the
following error in Excel ("The formula you typed contains an error").
Reading through the forum Excel sees to have a limit of 30 strings for the
CONCATENATE function.

However, the concatenate function only allow me to enter 14 fields. There
seems to be some sort of limits in total characters within concatenate
function (when I shorten some of the field names i.e. to F1, F2, F3 I could
enter more fields)

Please could you advice how I could maybe do multiple CONCATENATE. For
example, concatenate the first 30 strings, then concatenate the result of
that with the remaining 20 strings, etc?

Many thanks in advance,

 

[JLatham]

There are 2 ways to attack this. First is to concatenate a different way.
Instead of CONCATENATE() just use the ampersand ( & ) to merge the text, as:
="INSERT INTO Product(Field1,Field2) VALUES('" & B2 & "," & C2 & "')"
Much like CONCATENATE, but the & replaces the comma and you don't have the
CONCATENATE( at the beginning or the closing ) at the end.

You're no longer limited by the 30 strings although you are still limited by
formula length.

As for breaking the CONCATENATE() into several cells, you could simply start
working from left to right in the SQL statement you need to create. Lets say
you start building it up at column A, row 3:
in A3
="INSERT INTO Product "
in B3
=" (Field1, Field2, Field3, Field4, Field5)"
in C3
=" VALUES ('"
then in C4 you could have
=CONCATENATE(A3,B3,C3, ",B2,"','",C2,"',",D2,",",E2,",",F2,")")
or simply string them together as
=A3 & B3 & C3 & ",'" & B2 & "'," & "','" & C2 & "','" & "'," & D2 & "," &
"," & E2 & "," & F2 & ")"

That last might make seeing errors in pairing single quote marks up a little
easier also. I've not examined your example extremely closely, but it
appeared that you might have had doubld/single quote marks in wrong sequence
at times, which would give a formula error.

Hope this helps some

 

[will~]

Many thanks for your quick reply. Your answer is very clear and informative.
Due to the number of fields, the formula limit would restrict me from using
the first method. (I have tried replacing the comma with & for a smaller
table and it works well).

I would give the second method a try, and string together different parts of
the SQL statement.

kind Regards,

William

 

[Gord Dibben]

This UDF will allow many contiguous cells to be concatenated, comma-delimited.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(A2:CA2)

Will skip blanks if any in range.


Gord Dibben MS Excel MVP