Using Excel to Update SQL Data

The other day a co-worker was trying to figure out how to help a client update a lot of individual fields in a SQL table.  He did not want to grant them direct access to the entire backend of the SQL server table but he also did not want to have to spend a lot of time “developing” code to do this update.  This was a one-time update and using SSIS to create a package seemed like overkill.  He also wanted to have control over which fields got update.  He was trying to find an easy way of doing this.  His client also has some experience using spreadsheets.  I told him of a way to accomplish these requirements while at the same time giving his client the ability to “mass update” fields as needed.

Many people will take the an excel document and save it as a CSV or some other time and then use bulk load or some other tool to insert the records.  The problem with this is you may not want all fields or records updated.  I will explain a solution that many people forget about.  You can use formulas in excel to be the solution.  You can even use stored procedures to accomplish this.  I will give you screen shots for better clarity.

Let’s start with an example.  Suppose you want to update 900 customer records but you do not want all the fields updated only certain last names.

1. Open SQL Server Management Studio

2. Connect to the Database

3. Create a Select that returns all of the fields that you want to provide the user with (even the fields that you DO NOT want them to change) (It is important to export the unique key with this data)

4. When the results appear on the screen then right click on the results and ‘save results as’

5. Name the file and close SQL Server Management Studio.

6. Launch Excel and open the csv file.

7.  The ‘text import Wizard -Step 1 of 3’ should appear (Figure 1)

excel1(figure 1)

8. Hit Next and select Comma (make the setting from Figure 2).  If you have other delimiters than mark those instead.

excel2(Figure 2)

9. Change the column format (if necessary) and then hit Finish (Figure 3)

excel3(Figure 3)

10. You should have something that looks like Figure 4.

excel4(Figure 4)

11.  From here you can modify or lock down any of the column or cells you need to.  Once you are done then give it to the client to make changes.  Once they are done and give it back to you then the magic begins  (Figure 5)  NOTE: I have set the text to red for those changes necessary.  (I will explain later)

excel5(Figure 5)

12. In column D you will build your formula.  This formula is nothing more than an update sql statement. (Figure 6)

excel6(Figure 6)

13.  Once you have the right formula then copy the formula down the page.

14. Once you have done that then copy and paste the formula into SQL Server Management Studio (Figure 7)

excel8(Figure 7)

15. Execute it. –You are done.

Although I showed you a very simple example you could actually improve this like my co-worker did.  He actually told the users to highlight the cells that they changes and he then ran a macro to catch each style change and then use that to create the update formula.

This solution can be iterative as well.  If you use the same worksheet then you could update the data – send it back for more changes – and when you get it back all of your formulas are already there.  All you have to do is copy the formulas and execute them in SQL Server.

Another use is that you could actually EXECUTE a stored procedure and pass in the parameters.  Something like.

=”EXEC Update_Customers ” & A3 & “, ‘” & B3 & “‘”

The possibilities are endless.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , | Posted under MSSQL, Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *