Importing data from Other sources

We can also import data from other sources. Other data sources you can connect to include:

1. From SQL Server
2. From Analysis Services
3. From Windows Azure Market Place
4. From oData Data Feed
5. From XML Data Import
6. From Data Connection Wizard
7. From Microsoft Query

info

Now I describe one by one.

 

1. From SQL Server

Databases are great for storing information, but sometimes you want to use or analyze its data in Excel. You can use the Data Connection Wizard to create a dynamic connection between a SQL Server database and your Excel workbook. You get to the Data Connection Wizard through the Data tab.

  1. On the Data tab, in the Get External Data group, click From Other Sources.

Get External Data group on Data tab

  1. Click From SQL Server.
  2. In the wizard, enter the server name and logon credentials, and click Next.
  3. Choose the database and tables you want to work with, and click Next.
  4. You can click Finish, or click Next to change details for the connection.
  5. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.
  6. Click the Properties button to set advanced properties for the connection, such as OLAP settings or options for refreshing the connected data.
  7. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.
  8. Click OK to finish.

Important: Connections to external data might be disabled on your computer. To connect to data when you open a workbook, enable data connections by using the Trust Center bar, or save the workbook in a trusted location.

 

 

2. From Analysis Services

Analysis Services provides dimensional data that is well-suited for data exploration in PivotTables and Power View reports. You can get Analysis Services data from:

  • OLAP cubes on an Analysis Services multidimensional server.
  • Tabular models on an Analysis Services tabular server.
  • Excel 2013 workbooks on SharePoint 2013, if the workbook contains a data model.
  • Power Pivot workbooks on SharePoint 2010.

You can analyze data from an external Analysis Services data source using an online connection. As you pivot, slice, and filter a report, Excel queries Analysis Services for the data you asked for. Analyzing data in this way requires an online connection to the data source.

Another approach is to work offline. You can work offline by importing data into your workbook so that both data and report visualizations are self-contained and fully portable in a single file. If you do import data, you should know that doing so introduces new requirements, including the Power Pivot add-in, some knowledge of MDX, and sufficient disk space to store larger workbooks. This article explains how to import data from different Analysis Services data sources.

Excel workbooks that you save to SharePoint or Office 365 are subject to a maximum file size. By using the Table Import wizard in the Power Pivot add-in, you can selectively import tables, columns, and rows to reduce overall file size. If your workbook is too big to view in Excel Online, you can open it in Excel 2013 instead.Office 365 prohibits data refresh against external data sources, including Analysis Services solutions that run on servers in a network. If your requirements include refreshable data, choose SharePoint or use a network file share instead.

In this article
Prerequisites
Connect to a cube, tabular model, or Power Pivot data model
Import data from a cube
Import data from a tabular model
Import data from a workbook data model on SharePoint
Refresh data from an external Analysis Services database

Prerequisites

Analysis Services cubes must be version SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012.Analysis Services tabular model databases are only in SQL Server 2012.

You must know how to connect to an Analysis Services database. Check with your Analysis Services database administrator to determine server and database names, and which credentials to use. To import data into a data model, you must have the Power Pivot add-in that comes with Office Professional Plus. You might also need to know how to write an MDX query that retrieves the data you want to use. Alternatives to writing MDX query include using a query builder to choose which measures, dimension attributes, and hierarchies you want to analyze.

To connect to a PivotTable or PivotChart interactively via Excel, no MDX expertise is required. Excel connects to an entire OLAP cube or tabular model. File sizes will be bigger than what you might be accustomed to. By way of comparison, a workbook using a data connection might easily fall under 100 kilobyte, whereas the same workbook with imported data might be ten times the size. If disk space is an issue, you’ll need to make room for larger files.

Power Pivot workbooks that you use as data sources can be published to SharePoint 2010 or SharePoint 2013. The SharePoint site must be running on a different computer than the one you are using to create a report. You must have View permissions on the SharePoint site to report against workbooks on SharePoint.

 

Connect to a cube, tabular model, or Power Pivot data model

The easiest way to analyze data in an Analysis Services cube or model is to set up a connection to an external database. The PivotTable or report will have a live connection to the data source. Each time you drag a field onto a Values, Rows, Columns, or Filter area of a Fields List, Excel builds a query and sends it to Analysis Services.

Having a live connection to the data you’re analyzing has its advantages. You can perform free-form analysis. Add any field to a PivotTable or report, and Analysis Services gives back the data you requested. A Field List that’s connected to Analysis Services includes all of the objects in a cube or model, so you don’t have to write MDX to get the data you want.

Disadvantages include the dependency on a server connection. If the server goes down, or you want to work offline, data interaction stops completely.

Import data from a cube
Any data that is contained in a SQL Server Analysis Services database can be copied into a data model in Excel. You can extract all or part of a dimension, or get slices and aggregates from the cube, such as the sum of sales, month by month, for the current year.
The following procedure demonstrates how to use the add-in and MDX to get a subset of data from a traditional cube on an Analysis Service instance. MDX is always used when building a query using the Power Pivot add-in.

This procedure uses the Adventure Works DW Multidimensional 2012 sample database to explain how to import a subset of a cube. If you have access to an Analysis Services server that has the Adventure Works DW Multidimensional 2012 sample database, you can follow these steps to learn how to import data from Analysis Services.

  1. Start the Power Pivot add-in and open a Power Pivot window.
  2. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  3. In Connect to Microsoft SQL Server Analysis Services, in Server or File Name, type the name of the computer that runs Analysis Services.
  4. Click the down arrow to the right of the Database name list, and select an Analysis Services database from the list. For example, if you have access to the Adventure Works DW Multidimensional 2012 sample database, you would select Adventure Works DW Multidimensional 2012.
  5. Click Test Connection to verify that the Analysis Services server is available.
  6. Click Next.
  7. In the Specify a MDX Query page, click Design to open the MDX query builder.

    In this step, you drag into the large query design area all of the measures, dimension attributes, hierarchies, and calculated members that you want to import.

    Choose at least one measure and one or more dimensions.

    If you have an existing MDX statement that you want to use, paste the statement into the text box, and click Validate to make sure that the statement will work. For more information about how to use the designer, see Analysis Services MDX Query Designer (Power Pivot).

    For this procedure, using the Adventure Works sample cube as an example, do the following:

    1. In the Metadata pane, expand Measures, and then expand Sales Summary.
    2. Drag Average Sales Amount into the large design pane.
    3. In the Metadata pane, expand the Product dimension.
    4. Drag Product Categories to the left of Average Sales Amount in the large design area.
    5. In the Metadata pane, expand the Date dimension, and then expand Calendar.
    6. Drag Date.Calendar Year to the left of Category in the large design area.
    7. Optionally, add a filter to import a subset of the data. In the pane at the top right of the designer, for Dimension, drag Date into the dimension field. In Hierarchy, select Date.Calendar Year; for Operator, select Not Equal; for Filter Expression, click the down arrow and select CY 2009 and CY 2010.

      This creates a filter on the cube so that you exclude the values for 2009.

  8. Click OK, and review the MDX query that was created by the query designer.
  9. Type a friendly name for the data set. This name will be used as the table name in the data model. If you do not assign a new name, by default the query results are saved in a new table called Query.
  10. Click Finish.
  11. When the data has finished loading, click Close.

All data that you import from a cube database is flattened. In your model, the data shows up as a single table containing all of the columns you specified in your query. If you defined a query that retrieves measures along multiple dimensions, the data will be imported with each dimension in a separate column.

After you import the data into the data model you might want to check the data type of columns that contain numeric or financial data. If Power Pivot finds empty values in a column, it changes the data type to Text. You can verify and change the data type by selecting each column and viewing Data Type in the Formatting group on the ribbon. You can use the Data Type option to correct the data type if your numeric or financial data is assigned to the wrong type.

To use the data in a PivotTable, switch back to Excel:

  1. Click Insert > Table > PivotTable.
  2. Click Use an external data sources, and click Choose Connections.
  3. Click Tables.
  4. In This Workbook Data Model, select the table you just imported.

 

Import data from a tabular model

The following procedure describes data import using the tabular sample database as an example. You can use the sample, Adventure Works Tabular Model SQL 2012, running on an Analysis Services tabular server, to follow along.

  1. Make sure the tabular database contains at least one measure. Import will fail if the database is missing measures. If you’re using the sample solution, it includes measures.
  2. Start the Power Pivot add-in and open a Power Pivot window.
  3. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  4. In Connect to Microsoft SQL Server Analysis Services, in Server or File Name, type the name of the computer that runs Analysis Services.
  5. Click the down arrow to the right of the Database name list, and select an Analysis Services database from the list. For example, if you have access to the AW Internet Sales Tabular Model sample database, you would select Adventure Works Tabular Model SQL 2012
  6. Click Test Connection to verify that the Analysis Services server is available.
  7. Click Next.
  8. In the Specify a MDX Query page, click Design to open the MDX query builder.

    In this step, you drag into the large query design area all of the measures, columns, and hierarchies that you want to import.

    If you have an existing MDX statement that you want to use, paste the statement into the text box, and click Validate to make sure that the statement will work. For more information about the designer, see Analysis Services MDX Query Designer (Power Pivot).

    For this procedure, using the sample model as an example, do the following:

    1. In the Metadata pane, expand Measures, and then expand Internet Sales.
    2. Drag Internet Total Sales into the large design pane.
    3. Expand the Product table.
    4. Scroll to the bottom of the list and drag Category to the left of Internet Total Sales in the large design area. This is a hierarchy, so it will return all of the fields in the hierarchy.
    5. Expand the Date table.
    6. Drag Calendar Year to the left of Category in the large design area.
    7. Expand Sales Territory.
    8. Drag Sales Territory Region to the Filter area at the top of the designer. In Filter expression, select Australia.

      MDX query to sample tabular database

  9. Click OK, and review the MDX query that was created by the query designer.
  10. Type a friendly name for the data set. This name will be used as the table name in the data model. If you do not assign a new name, by default the query results are saved in a new table called Query.
  11. Click Finish.
  12. When the data has finished loading, click Close.

After you import the data into the data model you might want to check the data type of columns that contain numeric or financial data. If Power Pivot finds empty values in a column, it changes the data type to Text. You can verify and change the data type by selecting each column and viewing Data Type in the Formatting group on the ribbon. You can use the Data Type option to correct the data type if your numeric or financial data is assigned to the wrong type.

To use the data in a PivotTable, switch back to Excel:

  1. Click Insert > Table > PivotTable.
  2. Click Use an external data sources, and click Choose Connections.
  3. Click Tables.
  4. In This Workbook Data Model, select the table you just imported.

 

Import data from a workbook data model on SharePoint

SharePoint might need additional software to load the data. If you are using SharePoint 2010, you must have Power Pivot for SharePoint 2010. In contrast, SharePoint 2013 includes built-in capability for loading and querying a data model. If you are using SharePoint 2013, ask your SharePoint administrator whether Excel Services is enabled and configured for BI workloads.

  1. In the Power Pivot window, click Get External Data > From Database > From Analysis Services or Power Pivot.
  2. In Connect to Microsoft SQL Server Analysis Services, for Friendly connection name, type a descriptive name for the data connection. Using descriptive names for the connection can help you remember how the connection is used.
  3. In Server or File Name, type the URL address of the published .xlsx file. For example, http://Contoso-srv/Shared Documents/ContosoSales.xlsx.

    Note    You cannot use a local Excel workbook as a data source; the workbook must be published to a SharePoint site.

  4. Click Test Connection to verify that the workbook is available on SharePoint.
  5. Click Next.
  6. Click Design.
  7. Build the query by dragging measures, dimension attributes, or hierarchies to the large design area. Optionally, use the filter pane on the top right corner to select a subset of data for the import.
  8. Click OK.
  9. Click Validate.
  10. Click Finish.

Power Pivot data is copied to the data model and stored in a compressed format. After the data is imported, the connection to the workbook is closed. To re-query the original data, you can refresh the workbook in Excel.

 

Refresh data from an external Analysis Services database

In Excel, click Data > Connections > Refresh All to reconnect to an Analysis Services database and refresh the data in your workbook.

Refresh will update individual cells and add rows introduced into the external database since the last import. Only rows and existing columns are refreshed; if you want to add a new column to the model, you’ll need to import it using the steps provided earlier in this article.

Refresh re-runs the query used to import the data. If the data source is no longer at the same location, or if tables or columns are removed or renamed, refresh will fail, but you will still have the data that you previously imported. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window. Click Design > Table Properties to view the query.

Analysis Services uses your Windows user account to read data from its databases. Before you could import data, a database administrator had to grant your Windows user account read permissions on the database. These same permissions are also used to refresh data. If other people want to refresh the data, they also need read permissions on the database.

Remember that how you share your workbook will determine whether data refresh can occur. On Office 365, you cannot refresh data in a workbook that is saved to Office 365. On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

 

 

3. From Windows Azure Market Place

Microsoft Azure Marketplace features data from a variety of sources, including demographic, environment, financial, retail and sports data. Many sources are free. You can get data feeds to use this data in a Data Model that you build in Excel and in the Power Pivot add-in.

You need a Microsoft account to use data from Microsoft Azure Marketplace. If you are new to Azure Marketplace, try one of the free data sources to learn the steps. You can also Tutorial: Create a Power View report with Microsoft Azure Marketplace data.

Start from Microsoft Azure Marketplace
  1. Go to the Microsoft Azure Marketplace Web site.
  2. Log in using your Microsoft account.
  3. On first use, fill in account information and create an account ID to use when importing or exporting to Excel.
  4. Click Data.
  5. Under Price, click Free to list just those data sources that are free of charge.
  6. Click the data source. For most data sources, including free ones, you will need to sign up to use it. Click the link to sign up.
  7. On the data source page, look for Explore this Data. Most pages have a link with this name, that when clicked, opens a query window.
  8. Run the query.
  9. In Export to Program, choose Excel Power Pivot, and then click Download.
  10. When prompted to open or save the document, click Open.
  11. Click Create a New Workbook, and then click OK. Excel opens, starts the Power Pivot add-in, opens the Power Pivot window, and starts the Table Import wizard, with the data feed URL already specified.
  12. Power Pivot may ask for an Account ID. Find the Account ID on the My Account page on Azure Marketplace.
Start from Power Pivot
  1. In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window.
  2. On Home > Get External Data > From Data Service > From Microsoft Azure Marketplace.
  3. Microsoft Azure Marketplace opens on the Catalog page of the site. Select settings in the left to filter the data by type, price, category, and publisher. Browse and sign up for free or purchased datasets you can import directly into Power Pivot.

 

 

4. From oData Data Feed

Microsoft Excel® is a very popular tool for analyzing and visualizing data. Follow these instructions to create an Excel workbook with a link to data from any Socrata-powered website.

Because Excel maintains an active link to the data, all you need to do to refresh your workbook with the latest data is click the “Refresh All” button in the Excel ribbon.

Excel's Refresh All button

You will need the dataset’s OData endpoint.

Every dataset has a unique OData endpoint. When viewing a dataset select the “Export” pane and choose the OData section. Copy the OData endpoint to your clipboard.

Alternatively, the OData endpoint can be created via this simple rule:

http://$domain/OData.svc/$dataset_identifier

For example, the endpoint for the White House Visitors Record dataset would be:

http://open.whitehouse.gov/OData.svc/p86s-ychb

Note: OData connections are supported starting in the 2010 version of Microsoft Excel.

Step 1: Select the “Data” ribbon

Choose to “Get External Data” “From Other Sources” “From OData Data Feed”

Excel's From OData Data Feed button

Excel's Data Connection Wizard

Step 3: Check the box next to the dataset’s ID and click “Finish”

Excel's Data Connection Wizard

Result: Your data will appear in a table in Excel

Socrata data in Excel

Accessing Socrata data using Microsoft Power Query

Power Query for Microsoft Excel provides more querying capabilities than Excel’s default Data tab. Here’s how you can use Power Query to create a link to a Socrata dataset.

Step 1: Install the Power Query add-in

Download and install the latest version of Microsoft Power Query. You’ll need an installed version of Office 2010 Professional Plus, 2013 Professional Plus, Office 365 ProPlus, or a standalone edition of Excel 2013.

Step 2: Select the “Power Query” ribbon

Choose to “Get External Data” “From Other Sources” “From OData Feed”

Power Query's From OData Data Feed button

Power Query's From OData Feed dialog box

Step 4: Shape the data using Power Query

Note that you will want to use the column header to expand complex types like Locations and Website URLs. When finished, click Apply and Close.

Power Query's Query Editor

Result: Your data will appear in a table in Excel

Socrata data in Excel

Querying using OData

You can also directly query Socrata data using OData as a REST API.Socrata datasets follow the OData URI Conventions documented in http://www.odata.org/documentation/uri-conventions. For more information on OData, see http://www.odata.org/introduction

Here are some examples of how to apply these conventions to a Socrata catalog and dataset:

List all the datasets available for a domain.

https://sandbox.demo.socrata.com/OData.svc

Retrieve all the items in a particular dataset.

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp

Retrieve one item, by identifier

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp(16)

Retrieving a particular field within the item

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp(16)/depth/$value 

The OrderBy Query option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$orderby=magnitude

The Top Query option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$top=2

The Skip Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$skip=1005

The Filter Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$filter=magnitude%20gt%205

The Select Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$select=magnitude,depth

The InlineCount Query Option

https://sandbox.demo.socrata.com/OData.svc/nimj-3ivp?$filter=magnitude%20gt%205&$inlinecount=allpages
Socrata Datatypes exposed as OData

This table lists the OData types used to represent Socrata data.

Socrata Datatype ODataDatatype
Checkbox Edm.Boolean
Date & Time Edm.DateTime
Date & Time (with timezone) Edm.DateTimeOffset
Document Complext type with Edm.String listing the URL to the document and Edm.String listing the name of the document
Email Edm.String
Location Complex type with Edm.Decimal for latitude/longitude and Edm.String for Address, City, State and Zip
Money Edm.Decimal
Multiple Choice Edm.String
Number Edm.Decimal
Percent Edm.Decimal
Phone Complext type with Edm.String listing the phone number and Edm.String listing the phone type
Photo Edm.String listing the URL to the photo
Text (Formatted) Edm.String with HTML tags
Text (Plain) Edm.String
Website URL Complex type with Edm.String listing the URL and Edm.String listing the description

Four datatypes are not exposed via OData. These are: Star, Flag, Nested Table and Dataset Link.

Release Notes – February 2014

  • DateTimeZone filters using “before”, “after”, “between” in the Power Query editor will yield an error.
  • Pulling the entire opendata.socrata.com catalog is not supported because this domain has too many datasets. Linking to a dataset directly using the Data ribbon in Excel will work.

 

 

5. From XML Data Import

 

 

6. From Data Connection Wizard

The Data Connection Wizard allows connecting to databases, web data sources, and text files.

The following database platforms are supported:

  • Windows Azure SQL Database.
  • Microsoft SQL Server including Microsoft SQL Server Express LocalDB.
  • Microsoft SQL Server Compact.
  • Oracle Database.
  • IBM DB2.
  • MySQL.
  • MariaDB.
  • NuoDB.

You may connect to tables, views, and stored procedures.

The following web data sources are supported:

  • Web services including OData web services.
  • Web pages.

SaveToDB supports the following web and text data formats: HTML, XML, JSON, and CSV.

In general, the connecting includes the following steps:

  1. Selecting a provider.
  2. Connecting a database, web data source, or a text file.
  3. Selecting a connection object.
  4. Specifying parameters and customizing a query.
  5. Inserting a data table into Microsoft Excel.

The Data Connection Wizard can be used for changing or customizing an active query.

In this case, the wizard starts on the most suitable step.

Developers can customize the wizard.

Step 1. Selecting Provider

This step is used to select a provider to connect to a data source.

The wizard shows all installed providers available for connection.

Different providers can have specific features. The most important notes are shown in the comment field.

Microsoft Excel x64 requires 64-bit providers and Microsoft Excel x86 requires 32-bit providers.

The wizard shows only applicable providers.

OLEDB providers are preferable as Microsoft Excel allows customizing such queries much easy then others.

ODBC drivers allow working with data using Microsoft Excel native features too but have a little bit difficult customization.

.NET providers allow working with data using the SaveToDB add-in only but they do not require additional providers to be installed.

For example, Microsoft Excel has no support for Microsoft SQL Server Compact as there are no suitable OLEDB providers or ODBC drivers.

SaveToDB allows working with Microsoft SQL Server Compact as with other databases.

Data Connection Wizard - Selecting provider
Step 2. Connecting to Database, Web Data Source, or Text File

This step is used to connect to a database, web data source, or a text file.

The different database servers have specific features described in separate topics.

Click the Help button or the Examples link to get the context help during the connecting.

The Next button is enabled when the connection is successful. The wizard tests the connection in the background and activates the button.

If the Next button is disabled, click the Test Connection button to test a connection.

Data Connection Wizard - Connecting Excel to SQL Server database
Step 3. Select Database Object

Select an object to connect and its Query List on this step.

If you enable the Query List, you can change the query object further using the ribbon Query List, without using the Data Connection Wizard.
If you do not want to change the object later, uncheck Enable Query List on the ribbon.

The SaveToDB add-in saves the important metadata on hidden workbook worksheets.

If add-in’s worksheets are not added yet, you may add them by checking Enable SaveToDB in this workbook.
If the sheets are already added the field is disabled.

If the Enable SaveToDB in this workbook is unchecked, the wizard works like the native Microsoft Excel wizard.

SaveToDB contains the default Query List query that selects all tables, views, and stored procedures:

Data Connection Wizard - Selecting SQL Server database object

Developers can create different Query Lists to select objects by business areas.

 

For example, the following list is defined through the xls40.viewQueryList view and contains budget related objects:

Data Connection Wizard - Selecting SQL Server database object

Also developers can add SQL codes, http and text file queries to the Query List.

 

Step 4. Specifying parameters and customizing a query

The Query Parameters dialog box starts if the query (a stored procedure, SQL code, http, or a text) has parameters:

Data Connection Wizard - Specifying query parameters

This is a very useful feature absent in Microsoft Excel.
Step 5. Inserting Query

If the wizard has been started from an existing table, the wizard inserts the new query instead of the existing table.
It is useful to completely reconnect the existing query including restoring column native order.

Otherwise, the wizard asks an address to insert a table.

Insert a table at least at the second row to use the auto filter control row.
Also the first empty column makes the table more tidier.

Take a look on the screenshot:

  • The Query List allows changing the query objects.
  • The ribbon parameters allow changing query parameters.
  • The table is completely formatted as the example database contains saved format of the xls31.uspBudgetForm stored procedure.

Excel table connected to SQL Server database

Translating Database Objects

Database developers can configure data translation in databases to translate database objects, object fields and parameters within Microsoft Excel.

Here is the wizard page as shown above but with the translation:

Data Connection Wizard - Selecting SQL Server database object

The Query Parameters dialog box also shows translated parameter names:

Data Connection Wizard - Specifying query parameters

Customizing Tables and Views

The Query Parameters dialog box used for stored procedures is shown above.

The Query Parameters dialog box used for tables and views is shown below:

Data Connection Wizard - Specifying query parameters

Check the fields that should be selected in the S (Select) field.

Check the fields that should be used in the WHERE clause in the W (Where) field.

Take a look on the screenshot. The selected WHERE fields are placed on the ribbon and can be changed easily:

Excel table connected to SQL Server database

 

 

7. From Microsoft Query

This example teaches you how to import data from a Microsoft Access database by using the Microsoft Query Wizard. With Microsoft Query, you can select the columns of data that you want and import only that data into Excel.

1. On the Data tab, click From Other Sources, From Microsoft Query.

From Microsoft Query

The ‘Choose Data Source” dialog box appears.

2. Select MS Access Database* and check ‘Use the Query Wizard to create/edit queries’.

Choose Data Source

3. Click OK.

4. Select the database and click OK.

Select Database

This Access database consists of multiple tables. You can select the table and columns you want to include in your query.

5. Select Customers and click the > symbol.

Query Wizard - Choose Columns

6. Click Next.

To only import a specified set of records, filter the data.

7. Click City from the ‘Column to filter’ list and only include rows where City equals New York.

Query Wizard - Filter Data

8. Click Next.

You can sort your data if you want (we don’t do it here).

9. Click Next.

Query Wizard - Sort Order

10. Click Finish to return the data to Microsoft Excel.

Query Wizard - Finish

11. Select how you want to view this data, where you want to put it, and click OK.

Import Data

Result:

Microsoft Query Result

Note: when your Access data changes, you can click Refresh to update the data in Excel.

One thought on “Importing data from Other sources

  1. Awesome post.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment