Monday, February 23, 2015

Exporting data from SQL Server 2012 to an Excel file

The AdvTest.xls file was used in creating a PowerBI Preview report as described here:
http://hodentek.blogspot.com/2015/02/powerbi-preview-reports-using-data-on.html

The EXCEL file AdvTest.xls was created using the Export/Import Wizard that comes with an installation of SQL Server 2012. DTS Wizard alias Export/Import Wizard is an extremely versatile tool to import/export tables and view data.

The Wizard file is located here: C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTSWizard
You can launch the Export/Import Wizard by double clicking the above or from the SQL Server Management Studio.

SSMS_Wiz

The Choose a Data Source page is displayed. Provide the needed information as shown (the details (herein details are for the SQL server 2012 server on this laptop with the AdventurewOrks2012 database). The authentication is Windows. You only need to indicate the server name and then use drop-down to pick Adventureworks2012.

Excel1.png

 
Click Next. This brings up the Choose a Destination page as shown. As we need to get the SQL Server data into an Excel file use the drop-down handle to bring up Microsoft Excel as shown. Provide a path for the EXCEL file by including the full extension of the Excel file (Herein AdvTest.xls). This file will be created. The versions of Excel supported are as shown here. Note that the more recent versions are not supported.


ExcelVersions.png

Filled in details for the Excel file to be created are as shown in the next screen shot.
Excel2.png

Click Next opens the screen shown next.
Excel3.png

Accept the default and click Next. The Select Source Tables and Views window is displayed as shown.
Excel4.png

Click Next. The Review Data Type Mapping window is displayed as shown. Some changes to the choice of tables and views were made by going backwards and forwards from this screen..
Excel9x.png

Do not make any changes and click Next.

The Save and Run Package page is displayed. Accept defaults and click Next. After reviewing the transform details the final processing windows is displayed as shown.
Excel10.png

When operation is completed you will see the 'Success' window as shown:

ExcelFinal.png

This completes the exporting a set of tables and views from SQL Server 2012 to an Excel file. Few of the screen shorts were not shown. You will find the AdvTest.xls file in the path you provided earlier.

Note than on a x64bit Windows 8.1 machine you will not be able to view this file if Office is not installed on the machine.