Share via


Import Marketplace data into Excel

DataMarket Logo

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

  1. Start Microsoft Excel 2010.

  2. Click the Data tag.

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

    Figure 6.1 - DataMarket plugin in Excel

    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)

    Sign in to the Marketplace

    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)

      Select the Marketplace dataset to import
      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 6.3 - Filter your result set

    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 6.4. - Required and Optional Filters

    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.4 - Sort Results

    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 6.5 - Limit Number of rows returned

    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 6.6 - Specify fields to return

    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 6.7 - Data imported into Excel

    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