How to export a SQL query to Excel and pre-define formatting

Introduction

Many databases have options to export query results to Excel. Typically this is not really Excel, but just CSV and you have no option to format the output.

With AutoSQL you can write your query results directly to Excel and use a template to pre-define formatting.

In this example we will create a simple sales dashboard in Excel on top of the example Northwind database and save it as a template. We will setup AutoSQL to fill it with data and send the results weekly to the sales director by mail.

Step 1: Create a template

Let’s first create our template. We define two graphs based on two datasets.

The data is available in the 'Data' sheet:

Now let's delete the data in de ‘Data’ sheet and save the file as 'SalesYTDTemplate.xls'.

Step 2: Settings up AutoSQL

Our actionlist in AutoSQL needs to do two things:

  • Create a new Excel file based on the template and put the monthly sales amounts in the data sheet at cell A1
  • Append the created Excel sheet and put the sales per city in the data sheet at cell E1. After that, send an email with the results

Action 1: New Excel file based on template

Setup the query to report monthly sales. For more information on how to setup a query in AutoSQL have a look here.

Define the output. Set the output type to Excel and make it 'Overwrite' any existing file. This makes sure the template is used as a basis and a new file is created.

In the Excel options define the template with the graphs and the data sheet. Reference the 'Data' sheet as the sheet to use. Use cell A1 as the starting point.

Action 2: Append data and send mail

Now the second action needs to report the sales per city. We define this in the query:

In the output tab we define again Excel as the output type and the same file. However, now we choose to append the existing file which has been created in the first action.

We also define to send an email to the sales director with the report attached.

In the Excel settings we don’t need to specify the Excel template anymore. It has been used in the first action to create the file that we are now appending. We do specify to use the 'Data' sheet again and put the data in starting cell D1.

That’s it! Now schedule it weekly and make your sales director happy!