SubscribeSQL Queries from Excel
Often when you are working on 2 incompatible systems and try to import data from one to another, excel can be a handy tool. I have used excel plenty of times to generate SQL insert / update statements which I could later execute on the database console. Here is a tutorial if you ever have to use excel to generate SQL statements.
The example below shows a simple insert statement generated from customer data in a table. But you can easily extend this technique to come up with complex query statements.
1. Have your data ready
For our purpose the data is arranged like this:
As you can see, the data has to be in a tabular format so that you can easily generate the query statements. Often you may have to use lookup formulas to clean up the raw data imported in CSV formats.
2. Using excel operator ‘&’ to generate SQL query
Once the data is ready it is very easy to generate the SQL queries using excel string addition operator – &. For the above tabular structure, the concatenate formula would look like:
="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');" where B3, C3, D3 refer to above table data.
The final queries will look like:
There are a few practical ways to improve this:
- Cleaning up data using countif(), sumif(), if() formulas
- Using vlookup() or countif() to cross-reference items on one table to another
| ||
|
Spread some love,
Posts & Navigation
Tags: development, howto, Learn Excel, Microsoft Excel Formulas, programming, software, sql, technology, text processing |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
15 Responses to “SQL Queries from Excel”
I use this method very often.
I always use =SUBSTITUTE (ColumnWithText,”‘”,”””)
to be sure that potential apostrophe in text columns are doubled as required in SQL.
@Leonid.. that is a good technique to use substitute to clean up text apostrophes. thanks
Goal:
Generate update statement in excel where the columns that can be updated are dynamic
You want the columns which are not updated to keep the same value
(or not be overwritten with NULL values with the new generated statement)
the statement can be applied to multiple rows in excel for the same column headers
(This is why the ‘$’ exist for the column headers that are being set)
A1 = First_Name
B1 = Last_Name
C1 = Middle_Name
=”
UPDATE PERSONS “&CHAR(10)&
” SET 1 = 1 “&CHAR(10)&
IF(LEN(TRIM($A2))=0,”",”, “&$A$1&” = ‘”&$A2&”‘”&CHAR(10))&
IF(LEN(TRIM($B2))=0,”",”, “&$B$1&” = ‘”&$B2&”‘”&CHAR(10))&
IF(LEN(TRIM($C2))=0,”",”, “&$C$1&” = ‘”&$C2&”‘”&CHAR(10))&
” WHERE name = ‘staticordynamicvalue’ AND gender = ‘staticordynamicvalue’
”
Output (if all columns are set):
UPDATE PERSONS SET 1 = 1,
First_Name = ‘Joe’,
Last_Name = ‘ORien’,
Middle_Name = ‘Richard’
WHERE age = 28 AND gender = ‘m’
Output (if only First _Name (A1) is set):
UPDATE PERSONS SET 1 = 1,
First_Name = ‘Joe’
WHERE age = 28 AND gender = ‘m’
Possibly my post above is confusing without the actual table to look at. I will do the same example with the table used here. Instead of an insert statement I will generate an update statement for the columns, Cust_Name, Phone & E-mail
I hope this can help.
where we can generate an update statement for any column individually or together.
=”
UPDATE table “&CHAR(10)&
” SET 1 = 1 “&CHAR(10)&
IF(LEN(TRIM($A2))=0,””,”,Cust_Name = ‘”&$B3&”‘”&CHAR(10))&
IF(LEN(TRIM($B2))=0,””,”, Phone = ‘”&$C3&”‘”&CHAR(10))&
IF(LEN(TRIM($C2))=0,””,”, E-mail = ‘”&$D3&”‘”&CHAR(10))&
” WHERE Cust_Name = ’Bill Gates’
”
Thanks, it has been very useful !
It saved me at least 30 minutes, and time is the most expensive thing in our world…
Hey Paul,
What if any of A2, B2, or C2 is a date field?
The formula above is taking date as string. Any solution?
I’ve found the string concatenation method works well.
At the risk of sounding spammy I would mention that
if it’s something your are doing regularly it might be worth investigating a tools
that make it easier, such as QueryCell, an excel add-in I’ve developed.
It gives you a right click menu option that will produce and then customize insert statements for the selected region of Excel data.
Cheers
Sam
Hi,
For inserting the excel data to your SQL table, you can create insert statements in excel file according to your columns.
then just execute the statements all at once, it will insert the required data to sql server table.
thanks,
I tried to generate t-sql insert queries from the above example
=”insert into values(‘” &A2 &”‘,’” & B2& “‘);”
but it generates on one record instead of all records from excel sheet.
I’m using Excel 2003 and the excel sheet contains 922 records.
Most data bases can generate DDL for any object but not a lot of them allow generation of INSERT statements for the table data.
The workaround is to make use of ETL Tools for transferring data across servers. However, there exists a need to generate INSERT statements from the tables for porting data.
Simplest example is when small or large amount of data needs to be taken out on a removable storage media and copied to a remote location, INSERT..VALUES statements come handy.
There is a number of scripts available to perform this data transformation task. The problem with those scripts that all of them database specific and they do not work with textiles
Advanced ETL processor can generate Insert scripts from any data source including text files
http://www.dbsoftlab.com/generating-insert-statements.html
Super Aiticle. Thanks for this post.
I used to deal with the same problem, until found this awsome and free tool.
http://www.xtrategics.com/shapp/String%20Handler.application
regards,
Hi ,
i need a sql query to update a DB in excel 2010..
i have the query(SQL) for insert in excel as ,
=”insert into customers values(‘” &B3 &”‘,’” & C3 & “‘,’”&D3&”‘);”
similarly i need q sql query for update in excel
i want clear formulas only for insert,delete,update,select
Hi !
I would like to thank you so much ! This trick saves me a lot of time. Thank you so much. Really appreciate it !
-Ankit