Import Data Into Excel – Data Connection

SQL Server’s Import and Export Wizard is a great tool if we need to move data among many different sources.  However, if you only want to import data into Excel, then Excel also has a great end user tool that we can use to import data without any programming.

I am going to show you how to:

• Create Data Connection
• Retrieve Existing Data Connections
• Retrieve Data from Web

Data Connections

• We can create new data connection, select existing data connections within the Data Tab

Create Data Connection

We can create a data connection by clicking From Access, From Web, From Text or From Other Sources button.  We are going to create a data connection from SQL Sever.

• Click From Other Sources button.
• Click From SQL Server .

• Enter (local) in Server name.
• Click Next > button.

• Select AdventureWorks in Select the database that contains the data you want:
• Select vEmployee
• Click Next > button.

Click Finish button.

• Click Properties… button.

We can select when data should be refreshed, e.g. every nn minutes or when file is opened.

• Click OK button.

 

Click OK button again and data is now imported to Excel workbook.

 

We can manually refresh the data by clicking the Refresh All button. 

Retrieve Existing Data Connections

When we start a new workbook, we can also retrieve existing connections.

We can select the same data connection that we just created or choose other existing connections.

We can also create PivotTable and PivotChart

We can combine powerful Excel analytical tools and external data to make a smarter and more efficient decision.

We used SQL Server to demonstrate how to retrieve data from external source; however, we can use the same tool to get data from OLAP cube, text file, XML file and even web.

Retrieve Data from Web

Let’s select MSN MoneyCentral Investor Currency Rates from Existing Connections.

Within a few seconds, we would be able to retrieve currency rates from MSN Money.

Conclusion

Even if we don’t know any programming skill, there are many tools that we can use to retrieve external data.  Some of the tools also allow us to save data from Excel back to external data sources.  So we can enjoy the benefits from both worlds without being too technical.

• Import Data Into Excel – XML
• Import Data Into Excel – VBA
• Import Data Into Excel – Data Connection
• Import Data Into Excel – SQL Server Import and Export Wizard

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!