Category Archives: Excel

Jet Driver Error When Importing Data from Excel Files

Posted on by jeffprom

Importing data from files is common. Csv file formats are also common but can cause issues with characters such as commas. Excel files offer a nice solution as they are usually formatted properly and do not have issues with characters. However, there can also be a common problem scheduling a job to load the data due to a jet driver issue.

ImportData:Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

Here is how to overcome this error. First, install the ‘Microsoft Access Database Engine 2010 Redistributable‘ file which will provide the required jet driver.

Next, open the SSIS package/solution in visual studio and click on Project, <project name> Properties. On the Configuration Properties, Debugging tab, Set Run64BitRuntime = False. The jet driver is only 32 bit.

There is one final step and that is a configuration setting on the SQL Agent job itself. Without setting this, it will fail. Edit the job, click on Steps and edit the step. On the General tab, click on Configuration, Advanced and check the box that says 32-bit runtime.

That’s it! Execute the job and it should now finish successfully.

Advertisement

Formatting Power Query Columns

Posted on by jeffprom

Power Query is a great tool to quickly, and easily, load data into an Excel workbook. However, on the surface one potential drawback is the lack of formatting one can apply to the columns. This post will describe in further detail what I mean and show how to overcome this issue.

First, let’s take a look at how Power Query doesn’t work very well with formatting columns. Here I am pulling a set of data into Power Query and looking to format various columns with appropriate data types such as integer, currency, and date. I set the appropriate data types.

Whole Number

pq - whole number

Currency

pq - currency

Date

pq - date

Now that we set the data types, do a Close & Load to load the data into the Excel worksheet. We can inspect each column that we formatted and will see that the Whole Number and Currency turned into a General format. However, the Date data type actually does come through as a Date format.

Whole Number

pq - whole number - after load

Currency

pq - currency - after load

Date

pq - date - after load

Now let’s look at how we can actually format the number (integer), and currency columns. Yes, we can set it in Excel like normal but the problem is that when you refresh the data it will revert back to non-formatted columns. To get around this, click on the Data tab at the top and select Properties.

pq - data properties

Check the box that says ‘Preserve column sort/filter/layout’.

pq - preserve column layout

You can now format the columns in Excel like normal and it will retain formatting changes after data has been refreshed!

pq - format preserved

This works very well and you can format data just like normal in Excel. The only downside is that it is workbook specific. If you use the same query, possibly from the Power BI Data Catalog query, it will not retain the formatting changes and you will need to format each column again in every workbook you use.

Generating Dynamic CRUD Queries Within Microsoft Excel

Posted on by jeffprom

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.

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

Posted on by jeffprom

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

Posted on by jeffprom

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

Add Excel Chart Percentage Differences

Posted on by jeffprom

This isn’t exactly a SQL Server post, but it does fall in the realm of BI and analytics so I figured it was fair game. In this post, I will show you how to put some logic into your Excel column charts by adding a percent difference between data values. In our example we have some annual numbers and we want to know the annual growth percent. To accomplish this, we will create a chart like the one here.

Organize your data with the years across the top, and your numeric value directly below. On the 3rd line, create a new value called ‘% Change’. The first year % change will be 0, but the rest of the years will be Current Year / Last Year.

Drag your formula across all of the columns. Then highlight all 3 rows of data and click on Insert and insert a 2-D Column chart. One word of caution here is that it has to be a 2-D chart, and can’t be 3-D.

Now we have a new chart added. However, as you can see some of our numbers are very large, and some are very small. The ‘% Change’ numbers are hardly visible.

We will correct this by changing the ‘% Change’ on data to be on its own axis. To do this click once on the tallest blue bar on the right, wait about 1 second and then click it again. This should highlight just that one blue bar. Next, click the right arrow button on your keyboard and you should have the red values (% Change) highlighted. This is the best way to highlight this data series as the numbers are so small.

Now when the data series is highlighted, carefully hover your mouse over the data series until you see the pop-up window say ‘Series % Change…’. Next, right click and select the last option which is Format Data Series.

On this screen we want to select Plot Series on Secondary Axis and then click close.

Next, while the data series is still highlighted click on the Design tab, Change Chart Type, and select the first line chart option.

Now you can right click the data series and do some formatting such as Add Data Labels, and changing the Data Label Number type to Percentage. You can also right click and choose Format Axis on our new axis, and also set the Number to Percentage. Now you have a nice chart and can easily tell the annual growth percentage!