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:

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.


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.


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.


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

Click Next opens the screen shown next.

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

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..

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.

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


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.

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...