Tricks for generating SQL statements in Excel

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 & "')"

Answers


The semi-colon needs to be inside the last double quote with a closing paren. When adding single quotes around a string, remember to add them outside your selected cell.

(spaces added for visibility - remove before inserting)

=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")

Here is another view:

=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")


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

http://www.oneclickcommissions.com/excel-statement.html


the VBA approach would be: declare your string and assign your SQL statement like this

dim SqlString as String
SqlString = "SELECT * FROM %1 WHERE (var=%2)"
SqlString = Replace("%1", "Table_Name")
SqlString = Replace("%2", ""value"")

Excel approach is similar, but using the SUBSTITUTE function.

I prefer this approach because it makes the SQL text legible and avoids all the annoying & and concatenate problems. Yes, it takes an extra cell, but its worth it for the audit trail.


I know this pain. I ended up writing about it on my blog...twice. I created a UDF that concatenates a range of cells together with several options. This will always comma separate the values, but will also optionally add single quotes and/or parenthesis as needed.

So, you write the easy part of the sql statement.

INSERT INTO table
VALUES /*string that we don't want to type by hand*/

or

SELECT *
FROM table
WHERE foo IN (/*another string I don't want to type out*/)

And the custom excel function below will turn values in a spreadsheet range into a nice string for you.

Function SQLConcat(rng As Range, Optional quoted As Boolean = False, Optional parenthesis As Boolean = False) As String
' ***************************************************************
' * Returns a comma separated list for use in SQL IN statements *
' * Params *
' * - rng: Range of cells to concatenate *
' * - quoted: True/False. If true, values are placed inside *
' * of single quotes. Default of false *
' * - parenthesis: Boolean. *
' * Useful for INSERT INTO tbl VALUES(53),(90),(397) *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************

Dim tmp As String 'temporary string
Dim row As Long 'first cell is special case
row = 0 'initalize row count
Dim c As Object 'cell
Dim txtwrapperLeft As String, txtwrapperRight As String

If quoted = True And parenthesis = False Then
 txtwrapperLeft = "'"
 txtwrapperRight = "'"
ElseIf quoted = True And parenthesis = True Then
 txtwrapperLeft = "('"
 txtwrapperRight = "')"
ElseIf quoted = False And parenthesis = True Then
 txtwrapperLeft = "("
 txtwrapperRight = ")"
Else
'quoted = false and parenthesis = false
 txtwrapperLeft = ""
 txtwrapperRight = ""
End If

For Each c In rng.Cells
 If row = 0 Then
 tmp = txtwrapperLeft & c.Value & txtwrapperRight
 Else
 tmp = tmp & "," & txtwrapperLeft & c.Value & txtwrapperRight
 End If
 row = row + 1
 Debug.Print tmp
Next c

'return
SQLConcat = tmp
End Function

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.


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

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.


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];

For Inserting data into sql table

=CONCATENATE("INSERT INTO `database_name`.`table_name`(`Column_Name`,`Column_Name`) VALUES ( '",A1,"',",B1,"); ")

Need Your Help

WPF application crashes on windows 8.1

c# wpf windows-8.1

On windows 8.1 if I launch the application by the installer for first time it's working fine. However, if I try to open up the program manually with the 'exe' file or the shortcut on the desktop, the

File.exists() returns false when file exists

java file-io

I've encountered a bug I can't seem to find any logic behind. I have this File object, which is created like this: