Import Marketplace data into Excel

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

Import Data into Excel

1. Start Microsoft Excel 2010.

2. Click the Data tag.

3. Click the Marketplace add-in icon. (Figure 1)

   

   Figure 1 – Excel Marketplace Add-in

4. Select the Public DataMarket (default) radio button. (Figure 2.1)

5. If you are on a computer that is used by others, clear the Remember Me checkbox. (Figure 2.2)

6. Click Sign in or Create account. (Figure 2.3)

   

   Figure 2: Sign in to Azure Marketplace from Excel

7. When prompted whether or not to allow Excel to access your Marketplace account, click Allow Access.

8. 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

9. 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.

10. Click the Import Data button.

    

    Figure 9 – Data imported into Excel

See Also

Tasks

Compare Fixed and Flexible Query Types
Create Your Marketplace Account
Manage Your Marketplace Account
Subscribe to a Data Offer
Explore a Dataset with Service Explorer

Reference

Export to Excel PowerPivot or Tableau

Other Resources

Watch the video