Excel and SQL Jan22 '06

As a web developer, responsible for everything from the front-end design, to the back-end database - I can’t tell you how useful Microsoft Excel is, in combination with SQL.

Excel is typically considered a visual representation of a database, anyway - so immediately, there’s that relation.

Often times, as a client or co-worker presents me with an idea for a new database, they show me the columns and rows, from within Excel. This gives me an idea of what the database could look like.

Also, many valuable SQL front-end tools, such as phpMyAdmin, allow your data to be exported as a CSV file, specifically for Excel. Dumping the data into Excel is a fabulous way to immediately share the data with others, who may only have Microsoft Office on their computers.

So, as you can see, Excel and SQL tie hand-in-hand... and, to date, I don’t know of a better duo.

Excel text interaction

What many developers don’t know is that Excel is not only great for visualizing your SQL data - but it’s also great for manipulating your SQL data.

Let’s say a client gives you a spreadsheet, containing 3,000 records of data, that needs to be updated into the web database, so it can be immediately viewable on the corresponding web site.

Whereas you can export a CSV from SQL - you can’t just import a CSV file into SQL. You have to give SQL instructions on where and how that data will be stored, in the form of queries.

In Excel, let’s say you have two columns of corresponding data:

Account Name	Representative
Classic Tire Rental	James Carter
Gorgeous Pens, LLC	Rick Porter
Fluffy Furry Animals	Vanessa Smith

Let’s say there’s 3,000 accounts.

In your SQL database table, accounts, you already have the fields set up: accountName, and representative.

How do you quickly get all 3,000 records copied over to the database?

Easy.

Just write a formula, within Excel, to generate the SQL queries - and then copy/paste those queries into your SQL front-end tool.

Equal sign

In Excel, to begin writing a formula, go to an empty cell, and enter the equal sign (=).

The equal sign tells Excel that we’re about to enter a formula.

Strings

A string is simply a segment of text. Strings are always surrounded by quotes, either single or double. It’s typically better practice to use double-quotes, as in some programming languages, single quotes treat your strings differently.

For example, here is how you enter a string into Excel:

="My text string"

Into the cell that you entered that string, you’ll see the output:

My text string

On first glance, this is no different than simply typing My text string into the cell, rather than using the equal sign, and double-quotes. That’s true, but without the equal sign and double-quotes, you won’t be able to use the underlying programming language, to interact with your data.

Visual Basic

Excel is based on a programming language called Visual Basic. Visual Basic is a programming language, developed by Microsoft, which can be used to gain full control over your Excel data. In fact, Excel itself is written in Visual Basic.

We won’t delve too much into Visual Basic, but it’s important to know that the syntax we’re about to see is based on it.

Using cell values within strings

Our text string, again, looks like this:

="My text string"

Remember I said that using a string, like this, will allow you to interact with your data. Well... this is hardly interaction. All we did was manually type in that string into a cell.

What we’d like to do is interact with existing cells, each of which contain relevant data. We need to reference other cells, within our text string.

To do so, we simply "break out" of the string, temporarily:

="My text " & A1 & "string"

Notice the cell reference A1. Cell A1 already contains the words Account Name. So, our string output will now look like:

My text Account Name string

This is hardly sensical, but you should get the idea. By using the ampersand (&), and properly closing and re-opening our string quotes, we can reference any cell we’d like, within our text string.

Generating our queries

Back to our SQL example. We need to write a query, based on current cell values - for all of the values. So, that means 3,000 queries. Overwhelming? Not a chance. Excel makes this a breeze.

Into a blank cell, you can begin by entering this string:

="INSERT INTO accounts (accountName, representative) VALUES ('" & B1 & "', '" & B2 & "')"

Your cell output will be:

INSERT INTO accounts (accountName, representative) VALUES ('Classic Tire Rental', 'James Carter')

There’s your query!

Now... to copy this query for all data, simply drag the cell pointer downward, and the formula will reflect each row of data!

Categories: Efficiency , Excel , SQL , Tutorials

Add Feedback (view all)