Import data from external data sources (Power Query)

Import data from external data sources (Power Query)

Use Excel's Get & Transform (Power Query) experience to import data into Excel from a wide variety of data sources. You can then use the Query Editor to edit query steps to shape or transform data. For more information, see Shape data.

Data > Get & Transform > Get Data options

Note: You can still use Legacy Wizards if they are enabled in Excel Options (in the Data section). For steps, see the Office 2007 tab of this article.

Connect to a data source

Starting in Excel 2016, you use Get & Transform to connect to external data and perform advanced queries. It works mostly the same as Power Query, but it's not an add-in - it comes installed, and you'll find it on the Data tab of the ribbon. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

Using the Query Editor

Note: The Query Editor appears when you load, edit, or create a new query using Get & Transform. To view the Query Editor without loading or editing an existing workbook query, from the Get & Transform Data section in the Data ribbon tab, click Get Data > From Other Sources > Blank Query.

Query Editor in Excel 365

  1. Click on the Data tab, then Get Data > From File > select From Text/CSV. If you don't see the Get Data button, click on New Query > From File > select From CSV, or From Text.

    Note: You can also restore the Legacy Connectors to mimic earlier behavior. See the section on "How do I restore the legacy Get External Data experience?" in the following article: Unified Get & Transform.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Select any cell within your data range.

  2. Click the Data tab, then > From Table/Range.

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the table or range of data has column headers, you can check My table has headers. The header cells are used to define the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, Excel will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

You can use the Query Editor to write formulas for your query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Click on the Data tab, then Get Data > From File > select From Workbook. If you don't see the Get Data button, click on New Query > From File > select From Workbook.

    Note: You can also restore the Legacy Connectors to mimic earlier behavior. See the section on "How do I restore the legacy Get External Data experience?" in the following article: Unified Get & Transform.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for your query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Use Excel's Get & Transform experience to connect to a web page and import information from different tables.

  1. Click the Data tab, then New Query > From Other Sources > From Web.

    Note: If you don't see the New Query button, click the Data tab, then click From Web.

  2. In the From Web dialog box, enter a web page URL, then click OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then choose it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. Click on the Data tab, then Get Data > From Database > From SQL Server Database. If you don't see the Get Data button, click New Query > From Database > From SQL Server Database.

  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. Click the Data tab, then Get Data > From Other Sources > From ODBC. If you don't see the Get Data button, go to New Query > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

  1. Click the Data tab, then Get Data > From Database > From Microsoft Access Database. If you don't see the Get Data button, click New Query > From Database > From Access.

  2. In the Import Data dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. Click on the Data tab, then Get Data > From File > From XML. If you don't see the Get Data button, click New Query > From File > From XML.

  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

  1. Go to Data > Get External Data > From Database > From Analysis Services. If you don't see the Get Data button, click Get External Data > From Other Sources > From Analysis Services.

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

  6. Click Browse to change the default file location of My Data Sources, or check for existing file names.

  7. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  8. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  9. Click Finish to close the Data Connection Wizard.

  10. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  11. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

    • To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

    • To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

In Excel for Office 365:

  1. On the Data tab, click Get Data > From File > From JSON.

    Get Data from JSON file button

  2. Browse to your JSON file location, select it, and click Open.

  3. Once the Query Editor has loaded your data, click Convert > Into Table, then Close & Load.

In Excel 2016:

  1. On the Data tab, click New Query > From Other Sources > Blank Query.

  2. In the Query Editor, click Advanced Editor.

  3. Input your query string as follows, replacing "C:\Users\Name\Desktop\JSONTest.json" with the path to your JSON file.

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. Click on the Data tab, then Get Data > From Database > From Oracle Database. If you don't see the Get Data button, then click New Query > From Database > From Oracle Database.

  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Click on the Data tab, then Get Data > From Other Sources > From Sharepoint List. If you don't see the Get Data button, then click New Query > From Other Sources > From Sharepoint List.

  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

  1. Click on the Data tab, then Get Data > From Other Sources > From OData Feed. If you don't see the Get Data button, then click New Query > From Other Sources > From OData Feed.

  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note: Connect to an OData feed supports the JSON light data service format.

  1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

  2. In the From OLE DB dialog, enter your connection string, then press OK.

  3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

  4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

  5. In the Power Query Editor, press Close & Load.

  1. Click the Data tab, then Get Data > From Database > From MySQL Database. If you don't see the Get Data button, then click New Query > From Database > From MySQL Database

  2. Click the Data tab, then Get Data > From Database > From MySQL Database. If you don't see the Get Data button, then click New Query > From Database > From MySQL Database

  3. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  4. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  5. Click OK.

  6. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. Click the Data tab, then Get Data > From Other Sources > From Microsoft Exchange. If you don't see the Get Data button, the click New Query > From Other Sources > From Microsoft Exchange.

  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

  1. Click on the Data tab, then Get Data > From Other Sources > From Active Directory. If you don't see the Get Data button, then click on New Query > From Other Sources > Active Directory.

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Notes: 

  • This feature only available in Excel for Windows if you have Office 2019 or an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

  • Before you can connect to an SAP HANA database using Power Query, you need the SAP HANA ODBC Driver on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • You will need an SAP account to login to the website and download the drivers. If you are unsure, contact the SAP administrator in your organization.

To connect to an SAP HANA database:

  1. Click Data > New Query > From Database > From SAP HANA Database.

    SAP HANA database option in the Data tab
  2. In the SAP HANA Database dialog box, specify the server you want to connect to. The server name should follow the format ServerName:Port.

    SAP HANA Database dialog box
  3. Optionally, if you want to import data using native database query, click Advanced options and in the SQL Statement box enter the query.

  4. Click OK.

  5. If the SAP HANA server requires database user credentials, then in the Access an SAP HANA database dialog box, do the following:

    1. Click the Database tab, and enter your username and password.

    2. Click Connect.

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. Click on the Data tab, then Get Data > From Database > From IBM DB2 Database. If you don't see the Get Data button, then click New Query > From Database > From IBM DB2 Database.

  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. Click the Data tab, then Get Data > From Database > From PostgreSQL Database. If you don't see the Get Data button, then click on New Query > From Database > From PostgreSQL Database.

  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get & Transform Data > Get Data > From Online Services > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. Click the Data tab, then Get & Transform Data > Get Data > From Database > From Teradata Database.

  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

Important: Retirement of Facebook data connector notice   Import and refresh data from Facebook in Excel will stop working in April, 2020. You will still be able to use the Facebook Get & Transform (Power Query) connector until then, but starting in April, 2020, you will be unable to connect to Facebook and will receive an error message. We recommend revising or removing any existing Get & Transform (Power Query) queries that use the Facebook connector as soon as possible to avoid unexpected results.

Note: If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. On the Data tab, click Get Data > From Online Services > From Facebook. If you don't see the Get Data button, click New Query > From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note: Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query. To view the Query Editor without loading or editing an existing workbook query, on the Get & Transform ribbon tab, click Get Data > Launch Power Query Editor.

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Excel installation (32-bit or 64-bit).

  1. Click the Data tab, then Get Data > From Database > From Sybase Database. If you don't see the Get Data button, click New Query > From Database > From Sybase Database.

  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. Click the Data tab, then Get & Transform Data > Get Data > From Azure > From Azure Blob Storage. If you don't see the Get Data button, then click New Query > From Azure > From Microsoft Azure Blob Storage.

  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note: If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. Click the Data tab, then Get Data > From Azure > From Azure HDInsight (HDFS). If you don't see the Get Data button, click New Query > From Azure > From Microsoft Azure HDInsight.

  2. Enter the Account name or URL of the Microsoft Azure Blob Storage account associated with your HDInsight cluster, and click OK.

  3. In the Access Microsoft Azure HDInsight dialog box, enter your Account Key, and click Connect.

  4. Select your cluster in the Navigator dialog, and then find and select a content file.

  5. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

You can use the Power Query add-in to connect to external data sources and perform advanced data analyses. The following sections provide steps for connecting to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

Important: Before you can use Power Query in Excel 2013, you must activate it: click File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel.

Using the Query Editor

Note: The Query Editor appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, click From File > From CSV, or From Text.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. Select any cell within your data range.

  2. In the Power Query ribbon tab, click From Table.

    Connect to an Excel Data Table

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

You can use the Query Editor to write formulas for Power Query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, click From File > From Excel.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for Power Query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Note: While trying to import data from a legacy Excel file or an Access database in certain setups, you may encounter an error that the Microsoft Access Database Engine (Microsoft.ACE.OLEDB.12.0 provider) is not registered on the local machine. The error occurs on systems with only Office 2013 installed. To resolve this error, download the following resources to ensure that you can proceed with the data sources you are trying to access.

  1. Click the Power Query ribbon, then From Web.

  2. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. In the Power Query ribbon tab, click From Database > From SQL Server Database.

    Power Query From Database options
  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. Click the Power Query tab on the Ribbon, then select Get External Data > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

  1. In the Power Query ribbon tab, select From Database > From Access Database.

    Get data From Database dialog

  2. In the Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. In the Power Query ribbon tab, click From File > From XML.

    Power Query From File dialog
  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

    Excel  Ribbon Image

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

    Click Browse to change the default file location of My Data Sources, or check for existing file names.

  6. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  7. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. Click Finish to close the Data Connection Wizard.

  9. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  10. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

  1. On the Power Query tab, click From Other Sources > Blank Query.

  2. In the Query Editor, click Advanced Editor.

  3. Input your query string as follows, replacing "C:\Users\Name\Desktop\JSONTest.json" with the path to your JSON file.

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. In the Power Query ribbon tab, click From Database > From Oracle Database.

    Power Query From Database options
  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Sources > From SharePoint List.

    Power Query get data from Other Sources dialog
  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

  1. In the Power Query ribbon tab, select From Other Source > From OData Feed.

    Power Query get data from Other Sources dialog
  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note: Connect to an OData feed supports the JSON light data service format.

  1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

  2. In the From OLE DB dialog, enter your connection string, then press OK.

  3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

  4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

  5. In the Power Query Editor, press Close & Load.

  1. In the Power Query ribbon tab, click From Database > From MySQL Database.

    Power Query From Database options
  2. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Sources > From Microsoft Exchange.

    Power Query Data Sources
  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

Important: Make sure you have downloaded and installed the Power Query Add-In.

  1. In the Power Query ribbon tab, click From Other Sources > From Active Directory.

    Power Query get data from Other Sources dialog

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. In the Power Query ribbon tab, click From Database > From IBM DB2 Database.

    Power Query From Database options
  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. In the Power Query ribbon tab, select From Database > From PostgreSQL Database.

    Power Query From Database options
  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get Data > From Online Services > From Dynamics 365 (online).

    • If you don't see the Get Data button, click New Query > From Other Sources > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. In the Power Query ribbon tab, select From Database > From Teradata Database.

    Power Query From Database options
  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

Important: Retirement of Facebook data connector notice   Import and refresh data from Facebook in Excel will stop working in April, 2020. You will still be able to use the Facebook Get & Transform (Power Query) connector until then, but starting in April, 2020, you will be unable to connect to Facebook and will receive an error message. We recommend revising or removing any existing Get & Transform (Power Query) queries that use the Facebook connector as soon as possible to avoid unexpected results.

Note: If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. In the Power Query ribbon tab, click From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note: Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  1. In the POWER QUERY ribbon tab, select From Database > From SAP Sybase SQL Anywhere.

    Get External Data from a Database
  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. In the Power Query ribbon tab, select From Azure > From Microsoft Azure Blob Storage.

    Power Query Import from Azure dialog
  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note: If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. In the POWER QUERY ribbon tab, select From Azure > From Microsoft Azure HDInsight.

    Get external data from Azure
  2. In the Microsoft Azure HDInsight dialog box, enter an Account Name and click OK.

  3. Next, enter your Account Key, and click Connect.

    Note: If you need to retrieve your key, return to the Microsoft Azure Portal, select your storage account, and click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key and paste the value into the wizard.

  4. Select your cluster in the Navigator dialog, and then find and select a content file.

  5. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

You can use the Power Query add-in to connect to external data sources and perform advanced data analyses. The following sections provide steps for using Power Query to connect to your data sources - web pages, text files, databases, online services, and Excel files, tables, and ranges.

Important: 

  • Make sure you have downloaded, installed, and activated the Power Query Add-In.

  • To activate the Power Query add-in, click File > Options > Add-Ins. In the Manage section at the bottom, choose the COM Add-ins option from the drop-down list, then click Go. Click the Power Query check box, then OK. The Power Query ribbon should appear automatically, but if it doesn't, close and restart Excel.

Using the Query Editor

Note: The Query Editor appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. In the Power Query ribbon tab, click From File > From CSV or From Text.

  2. In the Comma-Separated Values Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

Note: If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. For example, if you imported the example CSV file below, Power Query automatically uses the first row as the column names and changes each column data type.

Example CSV file

Image of a CSV file

Power Query automatically changes each column data type:

  • Order ID changes to number

  • Order Date changes to date

  • Category remains text (the default column type)

  • Product Name remains text (the default column type)

  • Sales changes to number

In the Query Editor, Power Query automatically applies a FirstRowAsHeader step and ChangeType step. These automatic actions are equivalent to manually promoting a row and manually changing each column type.

After Power Query auto detects columns, you can also use the Query Editor to write formulas for Power Query. For example:

= Csv.Document(File.Contents("C:\Examples\Products Categories and Orders.csv"),null,",",null,1252)

= Table.PromoteHeaders(Source)

= Table.TransformColumnTypes(FirstRowAsHeader,{{"OrderID", type number}, {"CustomerID", type text}, {"EmployeeID", type number}, {"OrderDate", type date}, {"RequiredDate", type date}, {"ShipName", type text}})

  1. Select any cell within your data range.

  2. In the Power Query ribbon tab, click From Table.

    Connect to an Excel Data Table

  3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

    From Table dialog

  4. If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.

  5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

You can use the Query Editor to write formulas for Power Query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Query Editor Formula Sample

  1. In the Power Query ribbon tab, click From File > From Excel.

  2. In the Excel Browse dialog box, browse for or type a path to the file that you want to query.

  3. Click Open.

    If your source workbook has named ranges, the name of the range will be available as a data set.

You can also use the Query Editor to write formulas for Power Query. For example:

= Excel.Workbook
 (File.Contents("C:\Example\Products and Orders.xlsx"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window in Excel 2013 appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

  1. Click the Power Query ribbon, then From Web.

  2. In the From Web dialog box, enter a web page URL, then OK.

    Power Query > From Web > Input URL dialog

    In this case, we're using: http://en.wikipedia.org/wiki/UEFA_European_Football_Championship.

    If the web page requires user credentials:

    • In the Access Web dialog box, click a credentials option, and provide authentication values.

    • Click Save.

  3. Click OK.

  4. Power Query will analyze the web page, and load the Navigator pane in Table View.

    If you know which table you want to connect to, then click it from the list. For this example, we chose the Results table.

    Power Query > From Web > Navigator Table View

    Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.

    Power Query > From Web > Navigator > Web View
  5. Click Load, and Power Query will load the web data you selected into Excel.

  1. In the Power Query ribbon tab, click From Database > From SQL Server Database.

    Power Query From Database options
  2. In the Microsoft SQL Database dialog box, specify the SQL Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

    Power Query SQL Server Database connection dialog
  4. Select OK.

  5. Select the authentication mode to connect to the SQL Server database.

    Power Query SQL Server connection login credentials
    1. Windows: This is the default selection. Select this if you want to connect using Windows authentication.

    2. Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

  6. By default, the Encrypt connection check box is selected to signify that Power Query connects to your database using an encrypted connection. If you do not want to connect using an encrypted connection, clear this check box, and then click Connect.

    If a connection to your SQL Server is not established using an encrypted connection, Power Query prompts you to connect using an unencrypted connection. Click OK in the message to connect using an unencrypted connection.

Formula Example

You can also use the Query Editor to write formulas for Power Query.

= Sql.Databases(".")
= Sql.Database(".","Contoso")

  1. Click the Power Query tab on the Ribbon, then select Get External Data > From Other Sources > From ODBC.

  2. In the From ODBC dialog, if displayed, select your Data Source Name (DSN).

  3. Enter your connection string, then press OK.

  4. In the next dialog box, select from Default or Custom, Windows, or Database connection options, enter your credentials, then press Connect.

  5. In the Navigator pane, select the tables or queries that you want to connect to, then press Load or Edit.

  1. In the Power Query ribbon tab, select From Database > From Access Database.

    Get data From Database dialog

  2. In the Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Follow the steps in the Navigator dialog to connect to the table or query of your choice.

  4. Click Load or Edit.

  1. In the Power Query ribbon tab, click From File > From XML.

    Power Query From File dialog
  2. In the From XML Browse dialog box, browse for or type a file URL to import or link to a file.

  3. Click Open.

    After the connection succeeds, you will be able to use the Navigator pane to browse and preview the collections of items in the XML file in a tabular form.

You can also use the Query Editor to write formulas for Power Query. For example:

= Xml.Tables(File.Contents("C:\Downloads\XML Example.xml"))

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

    Excel  Ribbon Image

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

    Click Browse to change the default file location of My Data Sources, or check for existing file names.

  6. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  7. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. Click Finish to close the Data Connection Wizard.

  9. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  10. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

  1. On the Power Query tab, click From Other Sources > Blank Query.

  2. In the Query Editor, click Advanced Editor.

  3. Input your query string as follows, replacing "C:\Users\Name\Desktop\JSONTest.json" with the path to your JSON file.

    let
    
        Source = Json.Document(File.Contents("C:\Users\Name\Desktop\JSONTest.json")),
        #"Converted to Table" = Record.ToTable(Source)
    
    in
    
        #"Converted to Table"
    

Note: Before you can connect to an Oracle database using Power Query, you need the Oracle client software v8.1.7 or greater on your computer. To install the Oracle client software, go to 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio (12.1.0.2.4) to install the 32-bit Oracle client, or to 64-bit ODAC 12c Release 4 (12.1.0.2.4) Xcopy for Windows x64 to install the 64-bit Oracle client.

  1. In the Power Query ribbon tab, click From Database > From Oracle Database.

    Power Query From Database options
  2. In the Oracle Database dialog box, in Server Name specify the Oracle Server to connect to. If a SID is required, this can be specified in the form of “ServerName/SID”.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the Oracle server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Sources > From SharePoint List.

    Power Query get data from Other Sources dialog
  2. In the Microsoft SharePoint Lists dialog box that appears, enter the URL for a SharePoint site.

    Note: When connecting to a SharePoint list, enter the site URL instead of the list URL. In the Access SharePoint dialog box, select the most general URL to authenticate against the site correctly. By default, the most general URL is selected.

  3. Select OK to continue.

  4. In the Access SharePoint dialog box that appears next, select a credentials option:

    1. Select Anonymous if the SharePoint Server does not require any credentials.

    2. Select Windows if the SharePoint Server requires your Windows credentials.

    3. Select Organizational account if the SharePoint Server requires organizational account credentials.

  5. Select Connect.

    Excel Power Query connect to a Sharepoint List Connect dialog

  1. In the Power Query ribbon tab, select From Other Source > From OData Feed.

    Power Query get data from Other Sources dialog
  2. In the OData Feed dialog box, enter the URL for an OData feed.

  3. Select OK.

  4. If the OData feed requires user credentials, in the Access an OData feed dialog box:

    1. Select Windows if the OData feed requires Windows Authentication.

    2. Select Basic if the OData feed requires your username and password.

    3. Select Marketplace key if the OData feed requires a Marketplace account key. You can select the Get your Marketplace Account Key to subscribe to Microsoft Azure marketplace OData feeds. You can also Sign up for Microsoft Azure Marketplace from the Access an OData Feed dialog box.

    4. Click Organizational account if the OData feed requires federated access credentials. For Windows Live ID, log into your account.

    5. Select Save.

Note: Connect to an OData feed supports the JSON light data service format.

  1. Click the Data tab, then Get Data > From Other Sources > From OLEDB. If you don't see the Get Data button, follow the Data Connection Wizard instructions below.

  2. In the From OLE DB dialog, enter your connection string, then press OK.

  3. In the OLEDB Provider dialog, select from the Default or Custom, Windows, or Database connection options, enter the appropriate credentials, then click Connect.

  4. In the Navigator dialog, select the Database and tables or queries you want to connect to, then press Load or Edit.

  5. In the Power Query Editor, press Close & Load.

  1. In the Power Query ribbon tab, click From Database > From MySQL Database.

    Power Query From Database options
  2. In the MySQL Database dialog box, in Server Name specify the MySQL Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the MySQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

  1. In the Power Query ribbon tab, select From Other Sources > From Microsoft Exchange.

    Power Query Data Sources
  2. In the Access an Exchange Server dialog box, specify your Email Address and Password.

  3. Click Save.

  4. In the Microsoft Exchange Autodiscover Service dialog box, select Allow to allow the Exchange Service to trust your credentials.

Important: Make sure you have downloaded and installed the Power Query Add-In.

  1. In the Power Query ribbon tab, click From Other Sources > From Active Directory.

    Power Query get data from Other Sources dialog

  2. Enter your domain in the Active Directory dialog box.

  3. In the Active Directory Domain dialog box for your domain, click Use my current credentials, or Use alternate credentials. For Use alternate credenitals authentication, enter your Username and Password.

  4. Click Connect.

  5. After the connection succeeds, you can use the Navigator pane to browse all the domains available within your Active Directory, and drill down into Active Directory information including Users, Accounts, and Computers.

Warning: 

  • Before you can connect to an IBM DB2 database, you need the IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power Query installation (32-bit or 64-bit).

  • There are known issues reported by IBM installing the IBM DB2 Data Server Driver on Windows 8. If you are using Windows 8 and want to connect to IBM DB2 using Power Query, you need to follow additional installation steps. Find more information about the IBM DB2 Data Server Driver on Windows 8..

  1. In the Power Query ribbon tab, click From Database > From IBM DB2 Database.

    Power Query From Database options
  2. In the IBM DB2 Database dialog box, in Server Name specify the IBM DB2 Database Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. If the IBM DB2 server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Connect.

Note: Before you can connect to a PostgreSQL database in Power Query, you need the Ngpsql data provider for PostgreSQL installed on your computer. Select the driver that matches your Office version (32-bit or 64-bit). See: Which version of Office am I using? for more information. Also make sure you have the provider registered in the machine configuration that matches the most recent .NET version on your device.

  1. In the Power Query ribbon tab, select From Database > From PostgreSQL Database.

    Power Query From Database options
  2. In the PostgreSQL Database dialog box, specify the PostgreSQL Database Server you want to connect to in the Server Name section.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the PostgreSQL server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Select Connect.

  1. Navigate to https://web.powerapps.com/

  2. Select the Environment you want to connect to.

  3. In the menu, select the Settings icon > Advanced customizations > Developer resources.

  4. Copy the Instance Web API value. 

    Notes: 

    • The url format will be something like https://<tenant>.crm.dynamics.com/api/data/v9.0.

    • The exact format of the URL you will use to connect depends on your region and the version of CDS for Apps you're using. For more information see: Web API URL and versions.

  5. Select the Data tab, then Get Data > From Online Services > From Dynamics 365 (online).

    • If you don't see the Get Data button, click New Query > From Other Sources > From Dynamics 365 (online).

  6. In the dialog box, with the Basic option selected, enter the Web API URL for your CDS for Apps connection, and click OK.

    • If you select the Advanced option, you can append certain additional parameters to the query to control what data is returned. For more information see: Query Data using the Web API

  7. Select Organization account.

    • If you aren't signed in using the Microsoft Work or School account you use to access CDS for Apps, click Sign in and enter the account username and password.

  8. Click Connect.

  9. Within the Navigator dialog, select the data you want to retrieve.

  10. If the data is good to be imported as is, then select the Load option, otherwise choose the Edit option to open the Power Query Editor.

    Note: The Power Query Editor gives you multiple options to modify the data returned. For instance, you might want to import fewer columns than your source data contains. In that case, go to the Home tab > Manage Columns > Choose Columns, select the columns you want to keep, then click OK. When you're ready, click Close & Load to return the modified data to Excel.

Note: Before you can connect to a Teradata database, you need the .NET Data Provider for Teradata on your computer.

  1. In the Power Query ribbon tab, select From Database > From Teradata Database.

    Power Query From Database options
  2. In the Teradata Database dialog box, in Server Name specify the Teradata Server to connect to.

  3. If you want to import data using native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Select OK.

  5. If the Teradata server requires database user credentials:

    1. In the Access a Database dialog box, enter your username and password.

    2. Click Save.

Important: Retirement of Facebook data connector notice   Import and refresh data from Facebook in Excel will stop working in April, 2020. You will still be able to use the Facebook Get & Transform (Power Query) connector until then, but starting in April, 2020, you will be unable to connect to Facebook and will receive an error message. We recommend revising or removing any existing Get & Transform (Power Query) queries that use the Facebook connector as soon as possible to avoid unexpected results.

Note: If this is the first time you've connected to Facebook, you will be asked to provide credentials. Sign in using your Facebook account, and allow access to the Power Query application. You can turn off future prompts by clicking the Don't warn me again for this connector option.

  1. In the Power Query ribbon tab, click From Other Sources > From Facebook.

  2. In the Facebook dialog box, connect to Facebook using “Me”, your Username, or Object ID.

    Note: Your Facebook username is different from your login email.

  3. Select a category to connect to from the Connection drop-down list. For example, select Friends to give you access to all information available in your Facebook Friends category.

  4. Click OK.

  5. If necessary, click Sign in from the Access Facebook dialog, then enter your Facebook email or phone number, and password. You can check the option to remain logged in. Once signed in, click Connect.

  6. After the connection succeeds, you will be able to preview a table containing information about the selected category. For instance, if you select the Friends category, Power Query renders a table containing your Facebook friends by name.

  7. Click Load or Edit.

You can use the Query Editor to write formulas for Power Query.

= Facebook.Graph("https://graph.facebook.com/v2.8/me/friends")

Power Query Editor with Facebook formula

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Notes: 

  • Before you can connect to an SAP SQL Anywhere database, you need the  SAP SQL Anywhere driver installed on your computer. Select the driver that matches your Power Query installation (32-bit or 64-bit).

  1. In the POWER QUERY ribbon tab, select From Database > From SAP Sybase SQL Anywhere.

    Get External Data from a Database
  2. In the Sybase Database dialog box, specify the Sybase Server to connect to in the Server Name box. Optionally, you can specify a Database Name as well.

  3. If you want to import data using a native database query, specify your query in the SQL Statement box. For more information, see Import Data from Database using Native Database Query.

  4. Click OK.

  5. By default, the Encrypt connection check box is selected so that Power Query connects to your database using a simple encrypted connection.

  6. Click Connect.

Microsoft Azure Blob Storage is a service for storing large amounts of unstructured data, like images, videos, audio, and documents, which can be accessed from anywhere in the world via HTTP or HTTPS. For more information about Azure Blob Storage service, see How to Use Blob Storage.

  1. In the Power Query ribbon tab, select From Azure > From Microsoft Azure Blob Storage.

    Power Query Import from Azure dialog
  2. In the Microsoft Azure Blob Storage dialog box, enter your Microsoft Azure storage account name or URL, and then click OK.

  3. If you are connecting to the Blob storage service for the first time, you will be prompted to enter and save the storage access key. In the Access Microsoft Azure Blob Storage dialog box, enter your storage access key in the Account Key box, and click Save.

    Note: If you need to retrieve your storage access key, browse to the Microsoft Azure Portal, select your storage account, and then click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key, and then paste the value in the Account Key box.

  4. The Query Editor lists all the available containers in your Microsoft Azure Blob Storage. In the Navigator, select a container from where you want to import data, and then click Apply & Close.

  1. In the POWER QUERY ribbon tab, select From Azure > From Microsoft Azure HDInsight.

    Get external data from Azure
  2. In the Microsoft Azure HDInsight dialog box, enter an Account Name and click OK.

  3. Next, enter your Account Key, and click Connect.

    Note: If you need to retrieve your key, return to the Microsoft Azure Portal, select your storage account, and click on the Manage Access Key icon on the bottom of the page. Click on the copy icon to the right of the primary key and paste the value into the wizard.

  4. Select your cluster in the Navigator dialog, and then find and select a content file.

  5. Click Load to load the selected table, or click Edit to perform additional data filters and transformations before loading it.

Note: HDInsight has a default Hive table, HiveSampleData.txt, which you can use to learn how data is imported into Excel using Power Query. For a step-by-step guide about how to import data from HDInsight, see How to Connect Excel to Microsoft Azure HDInsight with Power Query.

Power Query is not available in Excel 2007. However, you can still connect to external data sources. Note that the experience is not as robust as the equivalent Get & Transform experience with Power Query. See: Unified Get & Transform.

Data Connection Wizard

Step 1: Create a connection with another workbook

  1. On the Data tab, click Connections.

    Connections

  2. In the Workbook Connections dialog box, click Add.

  3. Near the bottom of the Existing Connections dialog box, click Browse for More.

  4. Find your workbook, and click Open.

  5. In the Select Table dialog box, select a table (worksheet), and click OK.

    Notes: 

    • Worksheets are referred to as "tables" in the Select Table dialog box

    • You can only add one table at a time.

    • You can rename a table by clicking on the Properties button. You can also add a description.

  6. To add more tables, repeat steps 2 through 5.

  7. Click Close.

Step 2: Add the tables to your worksheet

  1. Click Existing Connections, choose the table, and click Open.

  2. In the Import Data dialog box, choose where to put the data in your workbook and whether to view the data as a Table, PivotTable, or PivotChart.

You can use the Data Connection Wizard to connect to an Access database.

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

    Get External Data group on Data tab

  2. In the Select Data Source dialog box, browse to the Access database.

  3. In the Select Table dialog box, select the tables or queries you want to use, and click OK.

  4. You can click Finish, or click Next to change details for the connection.

  5. In the Import Data dialog box, 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 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.

Go to the Data tab > Get External DataFrom Text. Then, in the Import Text File dialog box, double-click the text file that you want to import, and the Text Import Wizard dialog will open.

Step 1 of 3

Original data type    If items in the text file are separated by tabs, colons, semicolons, spaces, or other characters, select Delimited. If all of the items in each column are the same length, select Fixed width.

Start import at row    Type or select a row number to specify the first row of the data that you want to import.

File origin    Select the character set that is used in the text file. In most cases, you can leave this setting at its default. If you know that the text file was created by using a different character set than the character set that you are using on your computer, you should change this setting to match that character set. For example, if your computer is set to use character set 1251 (Cyrillic, Windows), but you know that the file was produced by using character set 1252 (Western European, Windows), you should set File Origin to 1252.

Preview of file    This box displays the text as it will appear when it is separated into columns on the worksheet.

Step 2 of 3 (Delimited data)

Delimiters    Select the character that separates values in your text file. If the character is not listed, select the Other check box, and then type the character in the box that contains the cursor. These options are not available if your data type is Fixed width.

Treat consecutive delimiters as one    Select this check box if your data contains a delimiter of more than one character between data fields or if your data contains multiple custom delimiters.

Text qualifier    Select the character that encloses values in your text file. When Excel encounters the text qualifier character, all of the text that follows that character and precedes the next occurrence of that character is imported as one value, even if the text contains a delimiter character. For example, if the delimiter is a comma (,) and the text qualifier is a quotation mark ("), "Dallas, Texas" is imported into one cell as Dallas, Texas. If no character or the apostrophe (') is specified as the text qualifier, "Dallas, Texas" is imported into two adjacent cells as "Dallas and Texas".

If the delimiter character occurs between text qualifiers, Excel omits the qualifiers in the imported value. If no delimiter character occurs between text qualifiers, Excel includes the qualifier character in the imported value. Hence, "Dallas Texas" (using the quotation mark text qualifier) is imported into one cell as "Dallas Texas".

Data preview    Review the text in this box to verify that the text will be separated into columns on the worksheet as you want it.

Step 2 of 3 (Fixed width data)

Data preview    Set field widths in this section. Click the preview window to set a column break, which is represented by a vertical line. Double-click a column break to remove it, or drag a column break to move it.

Step 3 of 3

Click the Advanced button to do one or more of the following:

  • Specify the type of decimal and thousands separators that are used in the text file. When the data is imported into Excel, the separators will match those that are specified for your location in Regional and Language Options or Regional Settings (Windows Control Panel).

  • Specify that one or more numeric values may contain a trailing minus sign.

Column data format    Click the data format of the column that is selected in the Data preview section. If you do not want to import the selected column, click Do not import column (skip).

After you select a data format option for the selected column, the column heading under Data preview displays the format. If you select Date, select a date format in the Date box.

Choose the data format that closely matches the preview data so that Excel can convert the imported data correctly. For example:

  • To convert a column of all currency number characters to the Excel Currency format, select General.

  • To convert a column of all number characters to the Excel Text format, select Text.

  • To convert a column of all date characters, each date in the order of year, month, and day, to the Excel Date format, select Date, and then select the date type of YMD in the Date box.

Excel will import the column as General if the conversion could yield unintended results. For example:

  • If the column contains a mix of formats, such as alphabetical and numeric characters, Excel converts the column to General.

  • If, in a column of dates, each date is in the order of year, month, and date, and you select Date along with a date type of MDY, Excel converts the column to General format. A column that contains date characters must closely match an Excel built-in date or custom date formats.

If Excel does not convert a column to the format that you want, you can convert the data after you import it.

When you have selected the options you want, click Finish to open the Import Data dialog and choose where to place your data.

Import Data

Set these options to control how the data import process runs, including what data connection properties to use and what file and range to populate with the imported data.

  • The options under Select how you want to view this data in your workbook are only available if you have a Data Model prepared and select the option to add this import to that model (see the third item in this list).

  • Specify a target workbook:

    • If you choose Existing Worksheet, click a cell in the sheet to place the first cell of imported data, or click and drag to select a range.

    • Choose New Worksheet to import into a new worksheet (starting at cell A1)

  • If you have a Data Model in place, click Add this data to the Data Model to include this import in the model. For more information, see Create a Data Model in Excel.

    Note that selecting this option unlocks the options under Select how you want to view this data in your workbook.

  • Click Properties to set any External Data Range properties you want. For more information, see Manage external data ranges and their properties.

  • Click OK when you're ready to finish importing your data.

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

  2. In the New Web Query dialog box, enter the address of the web page you want to query in the Address box, and then click Go.

    The web page opens in the New Web Query dialog box.

  3. In the web page, click the little yellow box with a red arrow next to each table you want to query.

  4. Set any options you want, then click Import.

Set Web Query options

  1. At the top-right corner of the New Web Query dialog box, click Options.

  2. In the Web Query Options dialog box, set any of the following options:

    Option

    Effects

    Formatting

    • None   The web data will be imported as plain text. No formatting will be imported, and only link text will be imported from any hyperlinks.

    • Rich text formatting only   The web data will be imported as rich text, but only link text will be imported from any hyperlinks.

    • Full HTML formatting   All formatting will be imported, and imported hyperlinks will be functional.

    Import <PRE> blocks into columns

    If this option is selected, each <PRE> block will be imported as a column.

    Treat consecutive delimiters as one

    This option only applies if the preceding option is selected. If this option is selected, delimiters that don't have any text between them will be considered one delimiter during the import process.

    Use the same import settings for the entire section

    This option only applies if the preceding option is selected. If this option is selected, data from the HTML <PRE> tags on the specified page is processed all at once during the import process. If not selected, the data is imported in blocks of contiguous rows so that header rows will be recognized as such.

    Disable date recognition

    If selected, dates are imported as text. If not selected, dates will be imported as date/time values.

    Disable Web Query redirections

    If selected, redirects will be ignored. If not selected, redirects will be processed.

The Get & Transform experience was not available in Excel 2007, so you can use an Office Data Connection (.odc) file to connect to a Microsoft SQL Server database from an Excel 2007 workbook. SQL Server is a full-featured, relational database program that is designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server.

    The Get External Data group on the Data tab

    The Data Connection Wizard starts. This wizard has three pages.

    Page 1: Connect to Database Server    

  2. In step 1, type the name of the SQL Server computer in the Server name box.

  3. In step 2, under Log on credentials, do one of the following:

    • To use your current Microsoft Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

      Security Note: 

      • Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: house1. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

      • It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

      Page 2: Select Database and Table    

  4. Under Select the database that contains the data you want, select a database. Under Connect to a specific table, select a specific table or view.

    Alternatively, you can clear the Connect to a specific table check box, so that other users who use this connection file will be prompted for the list of tables and views.

    Page 3: Save Data Connection File and Finish    

  5. Optionally, in the File Name box, revise the suggested file name. Click Browse to change the default file location (My Data Sources).

  6. Optionally, type a description of the file, a friendly name, and common search words in the Description, Friendly Name, and Search Keywords boxes.

  7. To ensure that the connection file is always used when the data is updated, click the Always attempt to use this file to refresh this data check box. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. To specify how the external data source of a PivotTable report is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings, and then select one of the following options to log on to the data source:

    • Windows Authentication     Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can affect performance when many users are connected to the server.

    • SSO     Select this option to use Single Sign On (SSO), and then enter the appropriate identification string in the SSO ID box. A site administrator can configure a Windows SharePoint Services site to use a Single Sign On database in which a user name and password can be stored. This method can be the most efficient when many users are connected to the server.

    • None     Select this option to save the user name and password in the connection file.

      Security Note: Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

      Note: The authentication setting is used only by Excel Services, and not by Excel.

  9. Click OK.

  10. Click Finish to close the Data Connection Wizard.

    The Import Data dialog box is displayed.

  11. Under Select how you want to view this data in your workbook, do one of the following:

    • To create an Excel table, click Table (this is the default).

    • To create a PivotTable report, click PivotTable Report.

    • To create a PivotChart and PivotTable report, click PivotChart and PivotTable Report.

      Note: The Only Create Connection option is available only for an OLAP database.

  12. Under Where do you want to put the data?, do one of the following:

    • To place the data in an existing worksheet, select Existing worksheet, and then type the name of the first cell in the range of cells where you want to locate the data.

      Alternatively, click Collapse Dialog Button image to temporarily collapse the dialog box, select the beginning cell on the worksheet, and then click Expand Dialog Button image .

    • To place the data in a new worksheet starting at cell A1, click New worksheet.

  13. Optionally, you can change the connection properties (and also change the connection file) by clicking Properties, making your changes in the Connection Properties dialog box, and then clicking OK.

    For more information, see Connection properties.

For Excel 2007, you can use Microsoft Query to connect to ODBC data sources.

Data Connection Wizard

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.

    Excel  Ribbon Image

    The Data Connection Wizard is displayed. This wizard has three panes.

    • Connect to Database Server

    • Select Database and Table

    • Save Data Connection File and Finish

  2. In the Connect to Database Server pane, in the Server name box, type the name of the OLAP database server.

    Tip: If you know the name of the offline cube file that you want to connect to, you can type the complete file path, file name, and extension.

  3. Under Log on credentials, do one of the following, then click Next:

    • To use your current Windows user name and password, click Use Windows Authentication.

    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

  4. In the Select the database that contains the data you want pane, select a database, then click Next.

    To connect to a specific cube in the database, make sure that Connect to a specific cube or table is selected, and then select a cube from the list.

  5. In the Save Data Connection File and Finish pane, in the File Name box, revise the default file name as needed (optional).

    Click Browse to change the default file location of My Data Sources, or check for existing file names.

  6. In the Description, Friendly Name, and Search Keywords boxes, type a description of the file, a friendly name, and common search words (all are optional).

  7. To ensure that the connection file is used when the PivotTable is refreshed, click Always attempt to use this file to refresh this data.

    Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.

  8. Click Finish to close the Data Connection Wizard.

  9. In the Import Data dialog box, under Select how you want to view this data in your workbook, do one of the following:

    • To create just a PivotTable report, click PivotTable Report.

    • To create a PivotTable report and a PivotChart report, click PivotChart and PivotTable Report.

    • To store the selected connection in the workbook for later use, click Only Create Connection. This check box ensures that the connection is used by formulas that contain Cube functions that you create and that you don't want to create a PivotTable report.

  10. Under Where do you want to put the data, do one of the following:

    • To place the PivotTable report in an existing worksheet, select Existing worksheet, and then type the cell reference of the first cell in the range of cells where you want to locate the PivotTable report.

      You can also click Collapse Dialog Button image to temporarily hide the dialog box, select the beginning cell on the worksheet that you want to use, and then press Expand Dialog Button image .

  11. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.

  12. To verify or change connection properties, click Properties, make the necessary changes in the Connection Properties dialog box, and then click OK.

Working with external data connections

The following sections discuss how external data connections work, and how to find edit, manage, and share the connection information with other programs and users.

Understanding the basics of data connections

Data in an Excel workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. This external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in to, and access the external data source.

The main benefit of connecting to external data is that you can periodically analyze this data without repeatedly copying the data to your workbook, which is an operation that can be time consuming and prone to error. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Connection information is stored in the workbook and can also be stored in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).

To bring external data into Excel, you need access to the data. If the external data source that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permissions, or other connection information. If the data source is a database, make sure that the database is not opened in exclusive mode. If the data source is a text file or a spreadsheet, make sure that another user does not have it open for exclusive access.

Many data sources also require an ODBC driver or OLE DB provider to coordinate the flow of data between Excel, the connection file, and the data source.

The following diagram summarizes the key points about data connections.

Connecting to external data sources

1. There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Access, other OLAP and relational databases, spreadsheets, and text files.

2. Many data sources have an associated ODBC driver or OLE DB provider.

3. A connection file defines all the information that is needed to access and retrieve data from a data source.

4. Connection information is copied from a connection file into a workbook, and the connection information can easily be edited.

5. The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.

Finding connections

To find connection files, use the Existing Connections dialog box. (On the Data tab, in the Get External Data group, click Existing Connections.) Using this dialog box, you can see the following types of connections:

  • Connections in the workbook    

    This list displays all the current connections in the workbook. The list is created from connections that you already defined, that you created by using the Select Data Source dialog box of the Data Connection Wizard, or from connections that you previously selected as a connection from this dialog box.

  • Connection files on your computer    

    This list is created from the My Data Sources folder that is usually stored in the My Documents (Windows XP) or Documents (Windows Vista) folder.

  • Connection files on the network    

    This list can be created from the following:

    • A set of folders on your local network, the location of which can be deployed across the network as part of the deployment of Microsoft Office group policies.

    • An Excel Services Data Connection Library (DCL) on a SharePoint Foundation site. 

Editing connection properties

You can also use Excel as a connection file editor to create and edit connections to external data sources that are stored in a workbook or in a connection file. If you don't find the connection that you want, you can create a connection by clicking Browse for More to display the Select Data Source dialog box, and then clicking New Source to start the Data Connection Wizard.

After you create the connection, you can use the Connection Properties dialog box (On the Data tab, in the Get External Data group, click Properties.) to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook and not the original data connection file that may have been used to create the connection (indicated by the file name that is displayed in the Connection File property on the Definition tab). After you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.

To ensure that the connection file is always used when a data source is refreshed, click Always attempt to use this file to refresh this data on the Definition tab. Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file, which must also have this property set.

Managing connections

By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them. (On the Data tab, in the Get External Data group, click Connections.) You can use this dialog box to do the following:

  • Create, edit, refresh, and delete connections that are in use in the workbook.

  • Verify the source of external data. You may want to do this in case the connection was defined by another user.

  • Show where each connection is used in the current workbook.

  • Diagnose an error message about connections to external data.

  • Redirect a connection to a different server or data source, or replace the connection file for an existing connection.

  • Make it easy to create and share connection files with users.

Sharing connections

Connection files are particularly useful for sharing connections on a consistent basis, making connections more discoverable, helping to improve security of connections, and facilitating data source administration. The best way to share connection files is to put them in a secure and trusted location, such as a network folder or SharePoint library, where users can read the file but only designated users can modify the file.

Using ODC files

You can create Office Data Connection (ODC) files (.odc) by connecting to external data through the Select Data Source dialog box or by using the Data Connection Wizard to connect to new data sources. An ODC file uses custom HTML and XML tags to store the connection information. You can easily view or edit the contents of the file in Excel.

You can share connection files with other people to give them the same access that you have to an external data source. Other users don't need to set up a data source to open the connection file, but they may need to install the ODBC driver or OLE DB provider required to access the external data on their computer.

ODC files are the recommended method for connecting to data and sharing data. You can easily convert other traditional connection files (DSN, UDL, and query files) to an ODC file by opening the connection file and then clicking the Export Connection File button on the Definition tab of the Connection Properties dialog box.

Using query files

Query files are text files that contain data source information, including the name of the server where the data is located and the connection information that you provide when you create a data source. Query files are a traditional way for sharing queries with other Excel users.

Using .dqy query files    You can use Microsoft Query to save .dqy files that contain queries for data from relational databases or text files. When you open these files in Microsoft Query, you can view the data returned by the query and modify the query to retrieve different results. You can save a .dqy file for any query that you create, either by using the Query Wizard or directly in Microsoft Query.

Using .oqy query files    You can save .oqy files to connect to data in an OLAP database, either on a server or in an offline cube file (.cub). When you use the Multi-Dimensional Connection Wizard in Microsoft Query to create a data source for an OLAP database or cube, an .oqy file is created automatically. Because OLAP databases aren't organized in records or tables, you can't create queries or .dqy files to access these databases.

Using .rqy query files    Excel can open query files in .rqy format to support OLE DB data source drivers that use this format. For more information, see the documentation for your driver.

Using .qry query files    Microsoft Query can open and save query files in .qry format for use with earlier versions of Microsoft Query that cannot open .dqy files. If you have a query file in .qry format that you want to use in Excel, open the file in Microsoft Query, and then save it as a .dqy file. For information about saving .dqy files, see Microsoft Query Help.

Using .iqy Web query files    Excel can open .iqy Web query files to retrieve data from the Web.

Using external data ranges and properties

An external data range (also called a query table) is a defined name or table name that defines the location of the data brought into a worksheet. When you connect to external data, Excel automatically creates an external data range. The only exception to this is a PivotTable report connected to a data source, which does not create an external data range. In Excel, you can format and lay out an external data range or use it in calculations, as with any other data.

Excel automatically names an external data range as follows:

  • External data ranges from Office Data Connection (ODC) files are given the same name as the file name.

  • External data ranges from databases are named with the name of the query. By default Query_from_source is the name of the data source that you used to create the query.

  • External data ranges from text files are named with the text file name.

  • External data ranges from Web queries are named with the name of the Web page from which the data was retrieved.

If your worksheet has more than one external data range from the same source, the ranges are numbered. For example, MyText, MyText_1, MyText_2, and so on.

An external data range has additional properties (not to be confused with connection properties) that you can use to control the data, such as the preservation of cell formatting and column width. You can change these external data range properties by clicking Properties in the Connections group on the Data tab, and then making your changes in the External Data Range Properties or External Data Properties dialog boxes.

Note: If you want to share a summary or report that is based on external data, you can give other people a workbook that contains an external data range, or you can create a report template. A report template lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

Understanding data source support in Excel and Excel Services

There are several data objects (such as an external data range and PivotTable report) that you can use to connect to different data sources. However, the type of data source that you can connect to is different between each data object. You can also use and refresh connected data in Excel Services, but there are additional limitations and workarounds that you should be aware of.

Excel data object and data source support

The following table summarizes which data sources are supported for each data object in Excel.

Supported
data source

Excel
data
object

Creates
External
data
range?

OLE
DB

ODBC

Text
file

HTML
file

XML
file

SharePoint
list

Import Text Wizard

Yes

No

No

Yes

No

No

No

PivotTable report
(non-OLAP)

No

Yes

Yes

Yes

No

No

Yes

PivotTable report
(OLAP)

No

Yes

No

No

No

No

No

Excel Table

Yes

Yes

Yes

No

No

Yes

Yes

XML Map

Yes

No

No

No

No

Yes

No

Web Query

Yes

No

No

No

Yes

Yes

No

Data Connection Wizard

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Microsoft Query

Yes

No

Yes

Yes

No

No

No

Note: These files, a text file imported by using the Import Text Wizard, an XML file imported by using an XML Map, and an HTML or XML file imported by using a Web Query, do not use an ODBC driver or OLE DB provider to make the connection to the data source.

Excel Services and data source support

If you want to display an Excel workbook in Excel Services (Excel in a web browser), you can connect to and refresh data, but you must use a PivotTable report. Excel Services does not support external data ranges, which means that Excel Services does not support an Excel Table connected to a data source, a Web query, an XML map, or Microsoft Query.

However, you can work around this limitation by using a PivotTable to connect to the data source, and then design and layout the PivotTable as a two-dimensional table without levels, groups, or subtotals so that all desired row and column values are displayed. For more information, see the links in the See Also section.

Understanding Data Access Components

Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows Server 2003 and Windows XP SP2. With MDAC, you can connect to and use data from a wide variety of relational and nonrelational data sources. You can connect to many different data sources by using Open Database Connectivity (ODBC) drivers or OLE DB providers, which are either built and shipped by Microsoft or developed by various third parties. When you install Microsoft Office, additional ODBC drivers and OLE DB providers are added to your computer.

Windows Vista and Windows 7 use Windows Data Access Components (Windows DAC).

To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.

To see a complete list of ODBC providers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.

You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database, or contact your database vendor.

Using ODBC to connect to data sources

The following sections describe Open Database Connectivity (ODBC) in more detail.

The ODBC architecture

In the ODBC architecture, an application (such as Excel) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (such as the Microsoft SQL ODBC driver) to connect to a data source (such as a Microsoft SQL Server database).

Defining connection information

To connect to ODBC data sources, do the following:

  1. Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.

  2. Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the registry or a DSN file, or a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

    To define a data source, in Windows Vista, click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. In Windows XP and Windows Server, click Start, and then click Control Panel. Click Performance and Maintenance, click Administrative Tools. and then click Data Sources (ODBC). For more information about the different options, click the Help button in each dialog box.

Machine data sources

Machine data sources store connection information in the registry, on a specific computer, with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer.

A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.

File data sources

File data sources (also called DSN files) store connection information in a text file, not the registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer with the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.

A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.

Using OLE DB to connect to data sources

The following sections describe Object Linking and Embedding Database (OLE DB) in more detail.

The OLE DB architecture

In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Excel), and the program that allows native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).

Defining connection information

A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:

  • In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider. For more information, see the section Importing data by using the Data Connection Wizard.

  • Create a blank text file with a .udl file name extension, and then edit the file, which displays the Data Link Properties dialog box.

Refreshing data

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

The following illustration explains the basic process of what happens when you refresh data that is connected to an external data source.

The basic process of refreshing external data

1. A refresh operation gets up-to-date data.

2. The connection file defines all the information that is needed to access and retrieve data from an external data source.

3. There are a variety of data sources that you can refresh: OLAP, SQL Server, Access, OLE DB, ODBC, spreadsheets, and text files.

4. Up-to-date data is added to the current workbook.

Excel provides many options for refreshing imported data, including refreshing the data whenever you open the workbook and automatically refreshing data at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status of the refresh while the data is being refreshed.

If your external data source requires a password to gain access to the data, you can require that the password is entered each time the external data range is refreshed.

Importing data programmatically and by using functions

If you are a developer, there are several approaches within Excel that you can take to import data:

  • You can use Visual Basic for Applications to gain access to an external data source. Depending on the data source, you can use either ActiveX Data Objects or Data Access Objects to retrieve the data. You can also define a connection string in your code that specifies the connection information. Using a connection string is useful, for example, when you want to avoid requiring system administrators or users to first create a connection file, or to simplify the installation of your application.

  • If you import data from an SQL Server database, consider using SQL Native Client, which is a standalone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB Provider and the SQL ODBC Driver into one native, dynamic link library (DLL), while also providing new functionality that is separate and distinct from the Microsoft Data Access Components (MDAC). You can use SQL Native Client to create new applications or enhance existing applications that can take advantage of newer SQL Server features, such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

  • The RTD function retrieves real-time data from a program that supports COM automation. The RTD COM automation add-in must be created and registered on a local computer.

  • The SQL.REQUEST function connects with an external data source and runs a query from a worksheet. The SQL.REQUEST function then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (XLODBC.XLA). You can install the add-in from Office.com.

For more information about creating Visual Basic for Applications, see Visual Basic Help.

Privacy Levels

  • Before you can combine data sources into specific data that match your data analysis requirements, you connect to a data source based on your data source Privacy Levels settings.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Microsoft Power Query for Excel Help

Import data from database using native database query

Data Management Experience in Power BI for Office 365 Help

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×