Connect to an Excel or CSV file (Power Query)

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

Note   For a very quick video on how to display Query Editor, see the end of this article.

In this article

Connect to an Excel or CSV file

Import data from a named range

Importing data from a CSV file

Formula Examples

Microsoft Access Database Engine (Microsoft.ACE.OLEDB.12.0 provider) is not registered error

Connect to an Excel or CSV file

  1. In the POWER QUERY ribbon tab, click From File > From Excel or From File > From CSV.

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

  3. Click Open.

    Important    If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. See importing data from a CSV file.

Import data from a named range

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

Named Range

Importing data from a CSV file

If you are importing data from a CSV file, Power Query will automatically detect column delimiters including column names and types. See importing data from a CSV file.

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

Sample CSV

Power Query automatically changes each column data type:

  • OrderID changes to number

  • CustomerID remains text (the default column type)

  • EmployeeID changes to number

  • OrderDate changes to date

  • RequiredDate changes to date

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.

Formula Examples

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

After Power Query auto detects columns:

= 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}})

Microsoft Access Database Engine (Microsoft.ACE.OLEDB.12.0 provider) is not registered error

While trying to import data from a legacy Excel file or an Access database in certain setups, you will 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.

Note    To ensure that you have the full set of features, install the Microsoft Access Database Engine 2010 Redistributableand all service packs.

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

Applies To: Excel 2013, Excel 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language