Generating Dynamic CRUD Queries Within Microsoft Excel

Last year I learned a neat trick from Ross McNeely. We kept getting ad-hoc Excel documents sent to us and we needed to update the databases with these values. Traditionally you might go through the import/export data wizard, or create an SSIS package to import the data. This usually leads to data conversion issues, possible temp tables, writing extra queries, and somtimes all around headaches.

This method shows how to write CRUD sql statements within Excel that can then update the database. This is a really quick way to take a handful of fields and update data with little effort. If you have a lot of fields you may want to consider going the more traditional route because it could take some time to write long query statments in Excel. That said, I have used this technique probably 100 times so I decided to post about it. Let’s take a look at how it works.

I have two examples here that will update two tables in the Adventure Works DW database. Let’s pretend that someone has just sent us an Excel file and needs product information updated in the DimProduct table. This is what the data looks like.

First, find an open cell off to the right (F) and begin writing the sql query as shown here. Begin with =” and place single quotes where the sql statement will require them. In order to reference other data from the row, place double quotes, an ampersand and then the cell location. To continue the rest of the statement simply place another ampersand and double quote.

Continue until the rest of the statement is complete. Here is what it should look like:
=”update DimProduct set EnglishProductName='”&B2&”‘, StandardCost='”&C2&”‘, FinishedGoodsFlag=”&D2&”, Color='”&E2&”‘ where ProductAlternateKey='”&A3&”‘”

Once finished, click off of the query statement. If it worked correctly you should now see the query populated with values. If you received some errors, go back and double check the statement. Syntax issues are the most common mistakes.

Now that we have one statement that looks correct, let’s quickly fill in all the other rows. Single click the cell with the query (F2), and then double click the green square on the lower right. This will populate the rest of the rows with the same query template.

After the rest of the rows are populated, you should now have a complete set of queries.

One last trick to note is when using dates. You will need to use the text() function as shown here and specify a date format.
=”update FactInternetSales set ShipDate='”&TEXT(C2,”yyyy-mm-dd”)&”‘ where SalesOrderNumber='”&A2&”‘ and SalesOrderLineNumber=”&B2

The final step is to copy the query statements from Excel and paste them into SQL Server Management Studio where you can simply execute them to update the database. In the previous screenshot you would click on column D, copy, and then paste into Management Studio, and execute.

And there you have it, writing queries directly within Excel provides a quick way to take data from an Excel spreadsheet and manage data within your database with very little effort.

Advertisements

Using the ‘Quick Analysis’ feature in Excel 2013 for some quick, yet powerful, analytics.

Last November while I was at PASS Summit, one of the presenters used Excel and added some data bars right on top of the data. I thought, ‘Wow, that’s cool!’ This is a new feature in Excel 2013 called ‘Quick Analysis’. This handy little tool offers some really quick and easy, yet powerful ways to visually analyze data. Let’s take a look at what it can do.

First, pick a set of data to analyze. For this example we are going to analyze some January metrics over a five year span. Highlight the actual data and not the headers. Notice on the bottom right there is a little icon. If you hover over it, you will see that it is the Quick Analysis tool. You can also use the Ctrl + Q shortcut.

quick analysis selecting data

The first menu option that pops up is the Formatting tab which has some neat features. The first option on the left is Data Bars. This gives a nice visual bar chart representation of each data value.

quick analysis data bars

The next option is Color Scale. This colors the data with red representing the low values, white for mid-range values, and green for high values. With a quick glance at the data we can easily see which values are high and which ones are low.

quick analysis color scale

The third option is Icon Set. KPI icons are shown such as a red arrow pointing down for low values, a yellow right-facing arrow for mid-range values, and a green arrow pointing up for high values.

quick analysis icon set

The fourth option is called Greater Than. This option highlights in red some of the higher values.

quick analysis greater than

Finally, the last formatting option is the Top 10%. This highlights in red the top 10% values.

quick analysis top 10 percent

Now let’s take a look at some of the other tabs in Quick Analysis. Let’s jump to the last tab called Sparklines. On this tab you have three options: Line, Column, and Win/Loss. Line shows a sparkline to the right side of your data and indicates over the data the trend, up/down/steady. In this screenshot we selected Column which shows a mini column chart to the right and indicates your data trend.

quick analysis sparkline columns

If we jump over to the Totals tab we can quickly add some totals to the bottom of our data such as Sum, Average, Count, % Total, Running Total, and more. In this example we are adding a % Total.

quick analysis percent total

As you can see our bland set of data is quickly turning into something we can easily analyze. To top it off why not add a quick chart? For the chart select all of the entered data including headers. Now on the Charts tab, we are presented with options such as Line, Stacked Area, Clustered Column, Stacked Column, etc. We are presented with a preview.

quick analysis line chart

Once selected, it adds the chart to the worksheet. The screenshot below shows the final product. One last tab that we didn’t show is Tables. There are two available options on this tab; Table and Blank PivotTable. Table in our case just adds a filter on our table headers. The second option, Blank PivotTable, allows us to create a PivotTable on a new worksheet with our data. One last thing to note is that you can stack all of the Formatting options. For example, you can do a Color Set with Data Bars and the Icon Set. It starts to get a little cluttered, but at least you have some options.

quick analysis final

Let’s review what Quick Analysis just helped us do. We can tell annual growth % from the totals on the bottom, the sparkline tells us the annual trend by day, the color formatting let’s us easily identify high and low values, and the chart gives us a great visual comparison of all of the metrics together. Talk about some powerful visuals, and it only took about a minute to do! Quick Analysis is a powerful new tool in Excel 2013 that provides a quick and easy way to analyze data.

Adding a 12 Month Trendline in Excel 2013

Trends over time can be a great way to track business health and goals. It’s common to track such metrics on a daily basis throughout the year. Week by week you may have several peaks and valleys. Adding a trend line is a good idea to help smooth out the spikes and better analyze the trending values throughout the year. In this post, I will show how to add a trend line.

First let’s start with collecting data. You should have a column for each day of the year, and another column for metric values you want to track.
Trend Line Data

Next, highlight all of the data values but do NOT highlight the column names. On the ‘Insert’ tab, click on the ‘Line Chart’. Your chart should like the one displayed here.

Insert Line Chart

Now that we have a line chart, let’s add the trend line. Right click on the chart line and click ‘Add Trendline’.

Add Trendline

The Trendline Options are now displayed. There are three tabs to work with; ‘Fill & Line’ (Left), ‘Effects’ (Middle), and ‘Trendline Options’ (Right).
We’ll take a look at each tab, but let’s first start on the right most tab (Trendline Options). Since we are adding an annual trend line, select the ‘Moving Average’ option and set the Period to 12 for 12 months.

Moving Average Trendline

Next, let’s jazz up the line a bit. Click on the left tab for ‘Fill & Line’ options. Here you can select all kinds of line options such as style, size, and color.

Trendline line options

The middle tab is for ‘Effects’ where you can add things such as glow, shadow, and soft edges. A couple of nice ones to set here are the Presets and Color. On Presets, select the top left Outer option called ‘Offset Diagonal Bottom Right’. On Color, select Black to give a nice drop shadow effect when combined with the above mentioned Presets option.

Trendline effects

After you are done formatting the trendline to your liking, simply close the Format Trendline options and you should now have a nice annual trendline.

Annual Trendline

Null values when importing from Excel with SSIS

I’ve written SSIS packages that import data from Excel spreadsheets and load them into SQL. However, I recently came across a situation where half the data in one of my columns were having their data values ignored and was written to the DB as Nulls. I tried changing the excel column format, etc with no luck. Perplexed I did some searching and came across an interesting little quirk. Basically when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found here.