Import Marketplace data into Excel

Article
02/23/2015

If you have an Marketplace account and the Excel Marketplace add-in, you can import data directly into your Excel worksheet. Once you have the data in your worksheet you can create graphs, do analysis on the data, and any other Excel action you desire.

Prerequisites

Before you proceed, you must have:

A valid Windows Live ID or OrgID.
If you need a Live ID go to the Windows Live home page and sign-up for a Live ID.
A valid Marketplace account. If you do not have a Marketplace account, go to Create Your Marketplace Account and follow the instructions there.
A subscription to a dataset of interest. If you have not subscribed to a dataset, go to Subscribe to a Data Offer and follow the instructions there.
The Excel Marketplace add-in installed. If you have not downloaded and installed the add-in go to the Azure Marketplace Add-In for Excel site, download, and install the add-in.

Sections in This Topic

Section	Description
Import Data into Excel	Follow these steps to import a Marketplace dataset into Excel.
Filter the Results	Follow these steps to import a filtered result set.
Sort the Results	Follow these steps to import a sorted result set. Flexible query datasets only.
Limit the Results	Follow these steps to import a limited number of records in the result set.
Specify the Result Fields	Follow these steps to specify the exact fields that are returned in the result set.

Import Data into Excel

Start Microsoft Excel 2010.
Click the Data tag.
Click the Marketplace add-in icon. (Figure 1)

Figure 1 – Excel Marketplace Add-in
Select the Public DataMarket (default) radio button. (Figure 2.1)
If you are on a computer that is used by others, clear the Remember Me checkbox. (Figure 2.2)
Click Sign in or Create account. (Figure 2.3)

Figure 2: Sign in to Azure Marketplace from Excel
When prompted whether or not to allow Excel to access your Marketplace account, click Allow Access.
After the list of datasets you’re subscribed to appears (Figure 3) you can
- Refresh the list – click the refresh icon. (Figure 3.1)
- Browse and subscribe to additional datasets – click the shopping care icon. (Figure 3.2)
- Import a specific dataset – click Import data… for the dataset you want to import. (Figure 3.3)
  
  Figure 3: Select the dataset to import
If your dataset is a flexible query dataset, you can filter, sort, limit the number of records, and specify the fields the query should return. If you want to filter, sort, limit the number of returned rows, or specify the fields that are returned, follow the appropriate steps below before you click Import Data.

Filter the Results
1. Click the Filter results tab.
2. Click Add filter.
3. From the first dropdown, select the field you want to filter on. (Figure 4.1)
4. From the second dropdown, select the relational operation you want to use for this filter. (Figure 4.2)
5. In the text box, type in the value you want to use for this filter. (Figure 4.3)
Figure 4 – Filter your result set

Repeat steps b through e for each additional filter you want to apply to your query.

If your dataset is a fixed query dataset, there can be required and optional filters. Click the Filter results tab to see if there are any required or optional filters. If there are required filters, fill in legitimate values before you continue. (Figure 5.1) Optional filters can be filled in or ignored. (Figure 5.2)

Figure 5 – Required and Optional Filters

Sort the Results
1. Click the Sort results tab.
2. Click the Add sort order button.
3. In the first dropdown, select the field you want to sort on. (Figure 6.1)
4. In the second dropdown, select whether you want to sort in Ascending or Descending order. (Figure 6.2)
Figure 6 – Sort the results

Repeat steps b through d for each additional field you want to sort on. The precedence of the field in the sort is the order they are listed. Figure 4 shows State is the primary sort field and Year the secondary sort field. The result set is then sorted by state and within each state subset by year. See Figure 7 for the result set.

Limit the Results
1. Click the Limit number of results tab.
2. If you want to change the maximum number of records returned, the default is 50, check the Limit the number of items returned to: check box (Figure 7.1).
3. Either type in or use the up/down arrows to set the maximum number of returned records (Figure 7.2).
  
  Note
  
  Marketplace does not return over 100 records in a single query.
Figure 7 – Limit the number of records returned

Specify the Result Fields
1. Click the Specify returned fields tab.
2. Select the check box for each field you want the query to return. (Figure 8)
Figure 8 – Specify the fields returned

If your dataset is a fixed query dataset, it can have required parameters. Click the Filter results tab to see and fill in any required parameters.
Click the Import Data button.

Figure 9 – Data imported into Excel