Using Excel to help with Data formatting (SQL Scripts)

(Download Excel Spreadsheet example at the bottom.)

Sometimes when developing I need to work with data. Sometimes this data is from external sources such as other partners in the business. Unfortunately when I get this data does not follow a consistent format therefore I’m left with formatting it for entry into a data store, usually a SQL Server.

In my last post about how to create store locator, I had this same problem. I had to import a list of company addresses for that tutorial. The only place I knew where to get a good list of locations for a store locator would be off of Starbucks Store Locator site. Through the trusty copy/paste method, I was able to grab about 200 store results in a matter of minutes from the website.

Now back to the original problem, the data format. The data looked like this after is was copied…

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462
United States

I needed it to look like this (for ease of use, plus its easier to debug):

1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462
 
Which will finally transform into this:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘1385 Metropolitan Ave (UCO)’, ‘1385 Metropolitan Avenue’, ‘Bronx’, ‘NY’, ‘10462’)

Hmmm… problem. Now I could take my time, write an SQL insert script for each and every one of these, but that would take forever.

Instead we can use Excel to do this for us.

First off, all credit for this sweet little trick goes to Chris Lawson (a developer I worked with during my time at Todd McFarlane Producitons, SPAWN.COM). He’s the one who showed it to me, I’m just showing it to you. 🙂

To get the data formatted into SQL insert scripts we need to use a few functions from Excel. We’ll mainly use LEFT, MID, RIGHT, TRIM and SEARCH. Using those 4 methods you can pretty much get anything you want out of the string, and thats what I’ve done.

Note: It might be very helpful if you downloaded my sample at the bottom of this post and read this while looking at the sample, just so you can see how it was done.

Step 1: Separate the Data

We’re going to want to take our data from this format:

1. 1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx NY, 10462

and organize it to:
 
1385 Metropolitan Ave (UCO)
1385 Metropolitan Avenue
Bronx
NY
10462

Which will eventually get transformed into SQL, like this …

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (’92nd & 3rd’, ‘1642 Third Avenue’, ‘New York’, ‘NY’, ‘101283618’)

a) Get the Title to look like: 1385 Metropolitan Ave (UCO)

Tasks:
 – Get rid of the “1. ” 

This will return the text in A1 without “1. ” in it.

This takes the contents in cell A1 (the text with the 1. 1385 Metro…) and gets rid of the 1. through some simple text manipulation. The SEARCH method takes a parameter to search for. Utilizing the single character wildcard “?” I’m able to find where the numbering ‘ends’ and then get the text after that utilizing the LEN and RIGHT functions.

I’m going to use the same method for each line…
 

Address

 

City

 

State

Zip

Step 2. – Creating the SQL

Now that we have all the fields separated, we can easily create a SQL Script off of this.
Simply concatinate them together.

(click for larger view)

Now that we have all of this done, we can copy, and paste the this code (or click and drag the bottom right corner) throughout the sheet.
This will create the fields and SQL for us.

Then, two lines down…

Which gives us…

Now, select the entire “C” COLUMN and copy it …

 

Now move that to any column down the line from column “C”, lets say, “E”. Right click and click “Paste Special”

 

Then click “Values”

 

Step 3 – Extracting the SQL

Now all the actual text values will be placed into the columns. You can now select Column “E” and copy all of those values.

The reason we have to paste special is because otherwise we’d be copying the column caluculations, instead of a pretty SQL script, we’d get a row with no data, such as:

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (”, ”, ”, ”, ”)

By pasting special, we’re telling Excel we want the VALUES of the cells, not the actual calculations.

Now that you’ve “pasted as special” with values, you can now copy column “E” and then paste that into your SQL Management Studio and run. For simplicity sake, here are a few rows from the actual Excel file that is attached to this post.

INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘1385 Metropolitan Ave (UCO)’, ‘1385 Metropolitan Avenue’, ‘Bronx’, ‘NY’, ‘10462’)
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘Whitestone’, ‘138-11 20th Avenue’, ‘Whitestone’, ‘NY’, ‘113562451’)
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘Target Flushing T-1150’, ‘13505 20th Ave’, ‘Flushing’, ‘NY’, ‘11356’)
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘LGA – Delta Flight Ctr-Delta Term’, ‘La Guardia International Airport’, ‘Flushing’, NY’, ‘11371’)
INSERT INTO dbo.Locations(LocationName, Address, City, State, Zip) VALUES (‘Flushing – Main Street’, ’41-02 Main Street’, ‘Flushing’, ‘NY’, ‘11372’)

Then execute your script and you’re on your way.

Conclusion

When is it appropiate to use this method? IT DEPENDS. If you have 10 records, no. If you have 1000 records? Yes. Its all about productivity. Ask yourself, is it going to be faster for me to spend 5 minutes writing this in EXCEL or is it going to take me 5 minutes to import them manually? This is up to you to answer. Hopefully this helps you on your next project. 🙂

Download File

starbucks-locations-xls.zip (52.71 KB)