How to Import Excel Power Pivot Data into Microsoft Access or Tableau
Question details
The user needs to understand why Power Pivot data models and connection-only queries are not directly importable into Access or Tableau and how to expose the data in a supported format.
- Product
- Microsoft Excel Power Pivot, Microsoft Access, Tableau
- Device & OS
- not provided
- Scenario
- A user has an Excel workbook with Power Pivot data and wants to analyze or store that data in Microsoft Access or Tableau.
- Observed behavior
- Access and Tableau do not show the Power Pivot model or connection-only query as a standard worksheet range or named range.
Before importing, open the Excel workbook and check whether the Power Pivot results are already exposed as a worksheet table or named range, and note the original data source used by the model.
Load Power Pivot Data into an Excel Worksheet Table Before Importing
Use this method when you need a standard table or named range that Access or Tableau can recognize.
Power Pivot stores data in a compressed model. Access and Tableau typically cannot read the model directly unless it is exposed through a worksheet table, range, or published source.
Open the Excel workbook that contains your Power Pivot data model.
In Excel, select the Power Pivot tab, click Manage Data Model, and then choose PivotTable > New Worksheet to return model fields to a worksheet. Alternatively, go to Data > Existing Connections and select the model.
Select the returned data range, click Insert > Table, and confirm that My table has headers is selected.
Save the workbook. In Access, go to External Data > New Data Source > From File > Excel; in Tableau, go to Connect > To a File > Microsoft Excel, and select the worksheet table or named range.
Connect Access or Tableau Directly to the Original Data Source
Use this method when the original database or source is available and you want to bypass the Power Pivot model.
Use WPS Office as a Free, Lightweight Excel-Compatible Spreadsheet Alternative
WPS Office is a free, lightweight alternative that opens and edits Excel workbooks with familiar formatting and functions. If you are working with standard worksheet ranges, tables, and CSV or database exports, WPS Spreadsheets helps you prepare data for Access and Tableau without the overhead of unsupported Power Pivot models.
- 1. Download and install WPS Office: Go to the official WPS website, download WPS Office, and install it on your Windows or Mac computer.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the .xlsx workbook to review standard worksheet tables, named ranges, and data exports.
- 3. Prepare data for Access or Tableau: Use familiar spreadsheet tools to keep data in regular worksheet tables or save a CSV or .xlsx file that Access or Tableau can import.
Frequently Asked Questions
Why can't Tableau or Access see my Excel Power Pivot model?
Power Pivot stores data in a data model that is not automatically exposed as a worksheet range or named range. Access and Tableau look for supported tables, ranges, or data connections, so you must first place the data in a worksheet table or connect directly to the source.
How do I make Power Pivot data importable into Access?
In Excel, return the Power Pivot results to a worksheet, format the range as an Excel table using Insert > Table, save the workbook, then import that table from Access via External Data > New Data Source > From File > Excel.
Can I import connection-only queries into Tableau?
Connection-only queries are not usually exposed as importable tables. In Tableau, connect to the original database with Connect > To a Server or use a published data source rather than trying to import the query definition.
Does WPS Office support Power Pivot data models?
WPS Office does not implement the Power Pivot data-model engine. You can open and edit standard Excel tables and ranges in WPS Spreadsheets, and use those regular worksheet tables to prepare data for Access or Tableau.
