Tech: Export SQL to Excel with script modifier

Export Data from SQL into Excel

Using a SQL script to modify the data set.

Earlier I covered a method for taking excel data and importing it into a SQL table using the import tool.  I also went over using Transact SQL Group By with the SUM function to sum data based on an item in a table.  Now we are going to look at exporting data from SQL into an excel file and we are going to use a transact SQL statement to modify the output.

Create an Excel file

First we will need to use Microsoft Excel to create an excel file. Headings are not needed in the file and the file won't need to be modified, we simply need to create a workbook and then save it as shown below.

Open the Import/Export Wizard

Once you've created your excel file open the wizard.  You will choose your data source which will be the server name and in this instance I chose the SQL Server native Client as the source type.  Below that you will choose the database from which you will be exporting the data.

Choose Destination

Choose Microsoft Excel as the destination and then choose the Excel file that you created earlier.

Specify Table or Query

In this instance we are actually going to run a small script so our export is formatted in a way that is easier for us to use, or formatted for another software package, etc.

SQL Query

Here you can see that we are using the query that we created in an earlier article to sum data in a table. 

If you click the Parse button you should see if any errors are generated by the SQL script.  Also if you click the browse button you should see a sample listing of the results.

Source and Destination Table

This screen shows the source and destination tables.  You can change the destination in this example because we are creating a new table(sheet) with the export.  You can also append data to a sheet but then you would want to choose the appropriate sheet.

You can preview the results and edit mappings to change data types or modify the mapping between the source and destination tables.

Save and Run

This is a neat step.  I won't be covering it in detail in this posting but in the future I will get in depth on this feature.  I have checked the Run Immediately box but if you were to save the package as a SSIS package you can then execute the package as a job or part of a maintenance routine.  This give quite a bit of latitude for automating repetitive processes.

Finalization Page 

There is a final confirmation page that goes over the export process you built in broad strokes.

Summary / Confirmation page

Finally we see a page that shows the success/failures of the process we've run.  You normally can click on informational messages and get more details, such as the "124 rows Transferred" link below.

Excel file - Final Results

As can be seen below our Excel file has been created and it has the information we expected in it.

Today was a day full of tech articles...and more to come, however I prefer science topics like the "Space Farming" or the "SE Asia Early Man" article I wrote in the past week or so.

Have a good one!

~Yati