I work in the IT Training team at a large organisation where we have a database that is used to store information about the courses that we deliver and the people who attend the courses. The database runs on SQL Server (a database application developed by Microsoft). Every week the Training Manager has to generate reports. These reports are generated in Excel using data stored in the SQL Server database. This short tutorial explains how to import the data into Excel using a simple Wizard.
Before you start you will need to know the name of the SQL Server and the logon details. You many need to obtain these from your SQL Administrator.
Step 1
Click the Data tab on The Ribbon
Click From Other Sources
Click From SQL Server
Step 2
Enter the name of the SQL Server into the Server name field.
If the server is configured to allow access based on a user’s Windows logon credentials leave Logon Credentials set to Use Windows Authentication. Otherwise select Use the following User Name and password and enter the user name and password for the SQL Server.
Click Next
Step 3
From the drop-down list, select the name of the database that you wish to connect to.
If you want to connect to a specific table within the database tick the Connect to a specific table box and select the table from the list.
Click Next
Step 4
The final step of the Data Connection Wizard is to save the Data Connection File. this file contains the information needed to enable Excel to connect to the database.
Specify a filename, a description and a Friendly Name. In this example I have used the same text for all. If Save password in file is NOT checked, whenever you try to connect to the database via this file you will be prompted for the connection password.
Click Finish
Step 5
Once the setup is complete, the next step is to import the data.
Specify how you want to view the data in the workbook and where you want the data to be located – in this example I’ve specified a Table that will start at A1 in the current worksheet
Click OK.
Step 6
If you did not check the Save password in file box in an earlier step, you will be prompted for the database password. Enter it and click OK.
A message is displayed indicating that the data is being retrieved and after a few seconds the data is displayed.
