Tricks for generating SQL statements in Excel

Question

Do you have any tricks for generating SQL statements, mainly INSERTs, in Excel for various data import scenarios?

I'm really getting tired of writing formulas with like

="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"

Answer 1

=concatenate("insert into table (id, name) values (",c2,",",d2,")";

and format c2 and d2 appropriately for text or number

or

Build the table and columns as a field as well.

=concatenate("insert into ",a1,"(",b1,") values (",c2,",",d2,")";

Answer 2

Why are you generating SQL in Excel? It's easier and much faster to export a worksheet as a CSV file, and then use some tool to import that file into a SQL database. For example MySQL's LOAD DATA INFILE statement.

Apologies for not answering your question directly, and I know that this isn't a solution for all circumstances.

Answer 3

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.

Answer 4

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).

Answer 5

Exporting an excel file as csv can be an alternative option (see Bill Krawin's post - as a new poster, I can't add comment yet). However be warned that you will probably have to change the formatting of your date fields to yyyy-mm-dd, otherwise the date columns will all show 00/00/00 – this is because MySQL uses a different date fomat to Microsoft Excel. Alternatively use OpenOffice to save the csv file.

Answer 6

How about querying and inserting the data from the Excel workbook to the source using ACE/Jet (a.k.a. Access) SQL? This requires an ACE/Jet which could be another Excel spreadsheet. Here's a quick example:

INSERT INTO 
   [ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=MyDatabase;UID=sa;Pwd=mypassword;].MyTable (ID, Name)
SELECT F1, F2
  FROM 
   [Excel 8.0;HDR=NO;IMEX=1;Database=C:\db.xls;].[Sheet1$A1:B4];

Answer 7

I used to use String concatenation method to create SQL inserts in Excel. It can work well but can also be a little time consuming and 'fiddly'.

I created an Excel Add-In that makes generating Inserts from Excel easier :

(see the video at the bottom of the page) http://www.howinexcel.com/2009/06/generating-sql-insert-statements-in-excel.html

http://www.querycell.com/SQLGenerator.html

Answer 8

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.