Change is part of life and our data is extremely susceptible to these changes. Can Microsoft Excel Keep up with all of the changes to your data? Maybe it’s time to use SQL to update your data.
Let’s discuss updating data in an effective way.
Up until now, many of the tutorials had examples where returns from using SQL over Excel were marginal. Excel is not well suited to update data without the help of VBA or some crazy plugins.
If you are an extreme excel user feeling your blood pressure rising over that last statement, just hear me out a second. Or send me a note if you have a better way.
IS IT OK TO MANAGE DATA IN EXCEL?
To change values in an Excel spreadsheet, Find and Replace is the best option (that I know of). Find and Replace has its limitations, including:
- Very Slow with lots of records
- Criteria for search is limited
- No Audit trail
Despite the shortfalls listed above, many users insist on managing all their updates in a spreadsheet. This isn’t always a terrible idea. depending on the circumstances, it might be.
Your current situation may make Excel a sufficient tool for data Management. However, data manipulation is an area where a strong foundation of SQL will give you an upper hand.
Here are factors that make Excel data management acceptable:
- Your access or knowledge of Databases is lacking. (Keep reading Excel2SQL Tutorials!)
- You don’t have a dependable DBA (Keep reading Excel2SQL Tutorials and do it yourself!)
- The amount of data you have is small and localized
- Infrequent or small Number of Changes to data
- Not dealing with Sensitive Data
MOVING TO SQL TO MANAGE YOUR DATA
After enough working knowledge of SQL under your belt, you will be able to determine the best tool for your circumstance. First, you’ll need that SQL foundation to get started.
A brief word of warning…
SQL provides a chance to improve the speed and precision of updates. It also comes with the opportunity to make a huge freakin mess if you don’t know what you are doing.
I’m here to guide you to that place where you know what you are doing. In another tutorial, you will learn SQL controls to protecting data from yourself and other nefarious users.
UPDATE SCRIPT DEMONSTRATION
We will use the sample data below for all of our practice scripts. The full table has 43 rows.
Let’s pretend all the Pencils in our inventory were actually Pens.
We could easily accomplish this in excel with a simple find and replace
UPDATE CELLS IN EXCEL
It’s good practice to preview rows affected before making any changes.
=COUNTIF(D3:D45,”Pencil)
13 Rows Returned by this COUNTIF formula.
A filter on “Item” would also allow us a sneak preview of changes.
Let’s make the update.
Select the “Item” column and Highlight all the cells in the column.
**Ctrl + Shift + Down**
Now Find and Replace. **Shortcut** Ctrl + H
13 Rows Updated! Just like we expected.
Let’s accomplish the same thing in SQL.
UPDATE ROWS IN SQL
Stick with the practice of regularly previewing our changes. The stakes are even higher in SQL without an undo button. This reminds me of a future conversation we will have.
You remember how to write a select statement right?
SELECT * FROM dbo.Inventory2
Where Item = ‘Pencil’;
OK, thirteen rows! Time to Update the Data.
The updated Statement has this format:
Update Table
Set Column = ‘New Field Value’
Where Column = ‘Old Field Value’;
Here’s the real-life execution.
Pretty simple right? And the matching row counts should provide some assurance.
UPDATE ROWS BASED ON MULTIPLE CONDITIONS
Now, what if you have to replace values based on multiple conditions.
Here’s our new scenario:
One of our Reps, Jones gets authorized a 10% discount on all Sales. Let’s also pretend the inventory table is a Sales Table!
UPDATE ROWS IN EXCEL BASED ON CRITERIA IN ANOTHER CELL
In Excel, begin by filtering on the Rep “Jones”. Then calculate the 10% off by Multiplying the Unit Cost by .90 in a separate cell. Paste the results of the Calculation as Text over the old Unit Cost.
**If I was using this table long term, I would add a discount field and make “Total” a Calculated field based on Units, UnitCost and Discount.
Wait! You have to make sure the rows are Sorted by Rep to Copy and Paste correctly.
Updating the Data in Excel was not terribly painful in this situation. However, it’s becoming more cumbersome.
What happens if we add some more complexity to our Update requirements.
WHAT IF THE UPDATE SCENARIO BECOMES MORE COMPLEX?
Rep Jones has authorized a 10% discount only on Binders, only for customers in the East Region. Also, the total has to reflect the new amount. Again, pretend the Inventory table is a Sales table(for me please, I don’t want to update the screenshot and examples)!
**A static Total field would be a lame design in an inventory or Sales system. A real-life table would definitely make this a calculated field.
MORE COMPLEX UPDATES IN EXCEL
In Excel you could do multiple sorts or calculate based on If statements, but do you want to go through all that? I don’t even feel like trying.
Let’s just do it in SQL!
UPDATING MULTIPLE FIELDS BASED ON MULTIPLE CONDITIONS IN SQL
Data manipulation in SQL is a breeze.
Our Scenario Again:
Rep Jones has authorized a 10% discount only on Binders, only for customers in the East Region, and reflect the Update in the Total.
Here we go again!
Always start by previewing changes with a select statement.
Select * from dbo.Inventory2
Where Region = ‘East’ AND Rep = ‘Jones’ AND Item = ‘Binder’;
The select statement states that 3 Rows will be affected.
Copy and Paste your Select statement. You will revisit the Select Statement after running the update. Then convert the copied Select Statement to an Update Statement.
To convert a select statement to an update statement, Replace the “SELECT * FROM” with “UPDATE”. Then insert the set conditions between the Update and Where Clause. Keep the where as is.
Select * from dbo.Inventory2
Where Region = ‘East’ AND Rep = ‘Jones’ AND Item = ‘Binder’;
Update dbo.Inventory2
set UnitCost = UnitCost * .9, Total = Total *.9
Where Region = ‘East’ AND Rep = ‘Jones’ AND Item = ‘Binder’;
Lets run this bad boy.
First, the Select Statement to preview our row count.
Then proceed with the update statement
Run the Select statement Again to view the changes. You better have made that copy as I told you!!
Looks pretty Nice!
TRACKING CHANGES TO ROWS
Excel doesn’t provide a straightforward Audit trail. SQL makes tracking changes much easier.
Logging details on changes made to your data is a great practice.
Excel is lacking in Audit capabilities unless you save a copy of the spreadsheet every time you make a change.
TRACKING CHANGES TO ROWS IN SQL
Let’s lead by example again.
Now we will add a “Time_stamp” to identify the time a record was last updated and “LastModifiedBy” to figure out “Whodunit?”.
We will set the “Time_stamp” equal to the current timestamp of the system. You can also put a static value like a note. “SqlCorrectionQ3Sale” might indicate all rows with this note had a correcting adjustment in the 3rd quarter sale.
A row version is a great addition, but too much for today’s tutorial
Here is our final script that Updates 3 rows and leaves an audit trail.
Tell me a simple way to do this in Excel and I will be really impressed.
