Connect to an Excel or CSV file (Power Query)

With Power Query, you can connect to an Excel workbook, text file (.txt), or Comma Separated Values (.csv) file just like you would connect to any other data source. The Power Query editor gives you a great deal of flexibility with how you get and transform your data.

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

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

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.

In the Power Query ribbon tab, click From File > From Excel, From CSV, or From Text. Follow the additional steps below.

Additional Steps

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

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

Image of the Power Query Navigator dialog

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

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

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

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

Import data from external data sources

Connect with an expert
Contact us
Expand your skills
Explore training

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.

×