Tricks for generating SQL statements in Excel

Sometimes I use substitute to replace patterns in the SQL command instead of trying to build the sql command out of concatenation. Say the data is in Columns A & B. Insert a top row. In cell C1 place the SQL command using pattern:

insert into table t1 values('<<A>>', '<<B>>')

Then in rows 2 place the excel formula:

=SUBSTITUTE(SUBSTITUTE($C$1, "<<A>>", A2), "<<B>>", B2)

Note the use of absolute cell addressing $C$1 to get the pattern. Especially nice when working with char or varchar and having to mix the single and double quotes in the concatenation. Compare to:

=concatenate("insert into table t1 values '", A2, "', '", B2, "')"

An other thing that has bitten me more than once is trying to use excel to process some chars or varchars that are numeric, except they have leading zeros such as 007. Excel will convert to the number 7.

I was doing this yesterday, and yes, it's annoying to get the quotes right. One thing I did was have a named cell that just contained a single quote. Type into A1 ="'" (equals, double quote, single quote, double quote) and then name this cell "QUOTE" by typing that in the box on the left of the lowest toolbar.

Sometimes, building SQL Inserts this seems to be the easiest way. But you get tired of it fast, and I don't think there are "smart" ways to do it (other than maybe macros/VBA programming).

I'd point you to avoid Excel and explore some other ideas:

• use Access (great csv import filter, then link to the DB Table and let Access handle the insert)
• use TOAD (even better import feature as it allows you to mix and match columns, and even to import from the clipboard)
• use SQL Loader (a bit tricky to use, but fast and pretty flexible).