Import data into Excel

Import data into Excel with a web services request (URI) specifying the Excel format.

You can use Excel 2003, Excel 2007 or Excel 2010 to import data with the web services. In Excel 2007 and 2010, there are limitations with refreshing XML data. For this reason, the HTML format is recommended.

Note: For Excel imports, set the suppress_error_codes parameter to true. Otherwise, requests that return any HTTP status code other than 200 (success) cause Excel to disable the connections for such requests. Setting suppress_error_codes=true ensures that all errors return a 200 status code to Excel, but the message text associated with the error still appears.
  1. Open Excel.
  2. Use Generator to create a URI for the report you want. Specify the HTML format (recommended), and ensure that you have a valid start period and end period for the report (data may not exist for all time periods).
  3. Copy the URI to the clipboard.
  4. Select the import option:
    Option Description
    Excel 2007 and 2010 logos.  SelectData > From Web.
    Note: The From Web option is recommended, because if you select Data > From Other Sources > From XML Data Import to import data from a secure data source, and then use Refresh All on the imported data, an error occurs.

    The New Web Query window opens.

    Excel 2003 logo.  SelectData > Import External Data > Import Data.

    The Select Data Source window opens.

    Note: The Select Data Source window provides local files for selection, but a URI is valid in the File name field.
  5. Specify the data:
    Option Description
    Excel 2007 and 2010 logos.  Paste the URI into the Address field and click Go.
    Excel 2003 logo.  Paste the URI into the File name field and click Open.

    The Connect to dialog box appears.

  6. For User name, provide your account and user names in the format account name\user name, for example: my_account\apiuser.

    If an error was suppressed, the error message text appears.

  7. Click the yellow arrow, then click Import.

    The Import Data dialog box appears with the existing worksheet and current cell selected.

  8. Click OK.