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

image

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 .

image

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

image

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

image

Click Finish button.

image

  • Click Properties… button.

image

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

  • Click OK button.

 image

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

image

 

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

image

Retrieve Existing Data Connections

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

image

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

image

We can also create PivotTable and PivotChart

image

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

image

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.

image

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

image

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.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

5 Responses to Import Data Into Excel – Data Connection

  1. Pingback: Import Data Into Excel – XML « Technologies and your business

  2. Pingback: Import Data Into Excel – SQL Server Import and Export Wizard « Technologies and your business

  3. Pingback: Import Data Into Excel – VBA « Technologies and your business

  4. Pingback: Is Excel our only option? « Technologies and your business

  5. Noah says:

    Pretty section of content. I just stumbled upon your site and in accession capital
    to assert that I acquire actually enjoyed account your blog posts.

    Any way I will be subscribing to your feeds and even I achievement you access consistently fast.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: