SQL 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