Wednesday, December 20, 2017

Run a Query in Excel and Remove Column Headers

For the past few weeks, I have been manually pulling data for our HR department to upload to a state run reporting site for nursing certifications. Basically, I run a query, copy the data from SSMS, and then paste it into an Excel template for them. Then, the HR rep uploads the data. Yesterday, I figured it was probably a better idea to embed a query in the Excel file and let them refresh it when needed.

The following is a step by step walk through of how I did it. For this example, I am using a query to return generic data from AdventureWorks since the data I was originally using contains clinical licensing information and doesn't need to be shown here.

Note: Also be aware that you will need to make sure the users of the file have permissions to query the database.

First, there is the Excel file. The one I was working with was a template for a data upload, so the first 7 rows were all used for column headers specifically to give instructions on using the template. Imagine, if you will, the most colorful, but otherwise boring, column headers you have ever seen. Actually, they look like this…

The first thing we need here is a data connection. In the toolbar, click on the Data tab and select ‘From Other Sources’. In the dropdown menu that opens, select ‘From SQL Server’.

Enter the server name, select your log on preferences, and click ‘Next’.

Select the database you would like to pull from. Below that, select the table. I selected only one of the tables I needed, since I used a query later on in the setup process anyway. Click ‘Next’.

On the last screen, there is the option to assign a friendly name and a description to the connection file. Just click ‘Finish’. This brings up the data import options. Here, we have to select the cell in with the return results will begin. As you can see in the below screenshot, I select cell B7. 

Next, click on the Properties button. In the Connection Properties window, select the Definition tab. This is where we can define what exactly we are looking for. Here, I have switched the ‘Command type’ to SQL. In the ‘Command text’ field, enter the query you would like to run. As mentioned before, in this example, I am just using query to pull in generic employee data from the AdventureWorks data warehouse. 

The query I am using is as follows…

Select e.Employeekey
From DimEmployee e
LEFT JOIN FactResellerSales r
                ON e.EmployeeKey = r.EmployeeKey
Where r.OrderDate between '2008-04-01' and '2008-05-01'

Click OK and you will receive a message about the original connection being changed. Just click Yes. Then click OK on the Import Data window.
The spreadsheet will be populated with the results of the query. However, as seen in the screen shot below, it includes the column headers.

Because of how this template is used, I do not want the column headers. This brings us to the reason I am posting this blog. I went insane trying to find a way to get rid of them.

First, make sure you have selected a cell in the table. It doesn't matter what you select, just click on something. This will make the ‘Design’ tab available in the tool bar.

There, take note of the check box next to ‘Header Row’. All you have to do is clear that box. That will clear the header row.

Now, select those empty cells, right click and select delete. Then, choose the option to shift the cells up. Now you can refresh the data. 

From this point on, any time the file is opened and the data is refreshed, the column headers will not be displayed.