ArcGIS Pro

Guide to connecting to Excel files in ArcGIS Pro

You are working with more data than ever before.  There are many data types, data collection methods, and data storage formats, and there’s an infinite amount of data available for use, including data from third parties such as Microsoft Excel files.

While the Work with Microsoft Excel files in ArcGIS Pro topic was updated to help you understand the available options for installing and using Microsoft Excel files in ArcGIS Pro; we understand that questions remain, such as the following:

  1. Do I need to have Microsoft products installed to connect to an Excel spreadsheet?
  2. How do I know which Microsoft Access Database driver to install?
  3. How do I know if a silent install is needed?
  4. How do I complete a silent install of the Microsoft Access Database Engine driver?

Let’s address each of these questions:

Question 1:  Do I need to have Microsoft products installed to connect to an Excel spreadsheet?

The short answer on this first question is no. Microsoft Office Excel tables may be used directly in ArcGIS Pro and do not require that Microsoft Excel or Microsoft Office be installed on the same machine with ArcGIS Pro. The only requirement is that the correct Microsoft Access Database Engine 2016 Redistributable driver is present. Without this driver installed, attempts to expand or add an Excel spreadsheet to ArcGIS Pro will encounter the following error message:

ArcGIS Pro Error message
ArcGIS Pro Error message: “Required Microsoft driver is not installed.”

Question 2:  How do I know which Microsoft Access Database driver to install?

To address this we need to first understand and verify a few things, and then we’ll get to the answer for this question.

Let’s begin by understanding why you need to install the Microsoft Access Database Engine driver. The Microsoft Access Database Engine technology helps bridge communication and data transfers between files that are proprietary to the Microsoft Office system and other non-Microsoft Office applications such as ArcGIS Pro.

There are two Microsoft Access Database drivers to select from:

Prior to installing the driver, you need to know if you have any Microsoft products already installed on your machine and if they’re installed using either the Click-to-Run (CTR) technology or a Microsoft Installer (MSI).

Microsoft Office is installed in one of two ways, either MSI or Click-to-Run (CTR). A Microsoft Installer (MSI) file, which more recently is referred to as a Windows Installer file, is typically one large download file that contains both the installer and all the Office components. Microsoft Office (CTR) is an alternative to the traditional (MSI) installer-based method and uses streaming and virtualization technology to install and launch Office products faster.  Knowing the type of installer used is important because if your Microsoft Office products were installed using CTR, (such as Microsoft Office 365), Microsoft’s side-by-side detection will prevent the installation of either the 32-bit or 64-bit driver from proceeding.

You also need to know the bit-level installed for these Microsoft products. This information is important because if you try to install the downloaded AccessDatabaseEngine.exe file on a machine that already has a Microsoft 64-bit application, you will get an error message saying you can’t install a 32-bit version of the database engine because you currently have 64-bit Office products installed. A similar error message will display if you try to install the AccessDatabaseEngine_X64.exe file on a machine that already has a Microsoft 32-bit application.

If you happen to encounter either of these issues mentioned above, don't despair!  There's a simple workaround, which involves performing a silent installation of the downloaded Microsoft Access Database Engine 2016 driver.

First, let’s verify the following three key points prior to downloading the appropriate driver for your configuration:

——> If no, proceed to Question 3.

——> If yes, proceed to Point 2 below.

http://Microsoft%20Word%20version,%20build%20and%20installer%20type%20details
Microsoft Word version, build and installer type details |
http://About%20Microsoft%20Word%20for%20Office%20full%20version%20number
Microsoft Word for Office full version number and bit version (32-bit or 64-bit) |

Question 3:  How do I know if a silent install is needed?

Based on your answers to the above questions, use the following decision tree as a guide to help you determine the appropriate Microsoft Access Database driver to install and to determine if a silent install is needed.

Decision tree guide
TIP: Click the image above to see a larger version of this decision tree guide.

After reviewing this guide, do you need to perform a silent install?

IMPORTANT: If you are not sure whether a silent install is needed or if you happen to have both 32-bit & 64-bit Microsoft products installed, download the Microsoft Access Database Engine 2016 Redistributable 64-bit driver (AccessDatabaseEngine_X64) and follow these steps outlined below in Question 4 to complete a silent install of this driver.

Question 4:  How do I complete a silent install of the Microsoft Access Database Engine driver?

NOTE: Before beginning, ensure your ArcGIS Pro application is closed.

Complete the following steps to perform a silent install of the Microsoft Access Database engine driver:

Step 1. Download the Microsoft Access Database Engine 2016 Redistributable and save the file locally to your machine.

Download button for the Microsoft Access Database Engine 2016 Redistributable driver
Choose between the 32-bit or 64-bit driver version

Example:

On my local machine where ArcGIS Pro resides, I created a new folder named MS_Driver.

In this example, here’s the path (highlighted) where the 32-bit driver has been saved locally on my machine.

The local file path to the installed 32-bit driver

In this example, here’s the path (highlighted) where the 64-bit driver has been saved locally on my machine.

The local file path to the installed 64-bit driver

Step 2. Open the Command Prompt and click Run as administrator.

Windows Taskbar search results

Step 3. From the command prompt, run a silent install of the driver.

Example:

Here’s the full path of the 32-bit driver .exe file run as a silent install:

C:\MS_Driver\accessdatabaseengine.exe /quiet

Silent install of 32-bit driver from command prompt

Here’s the full path of the 64-bit driver .exe file run as a silent install:

C:\MS_Driver\accessdatabaseengine_X64.exe /quiet

Silent install of 64-bit driver from command prompt

Step 4. Verify the driver was installed successfully.

Add or remove programs

Example:

If the 32-bit driver was installed, it will show 137 MB as the size of the driver.

The 32-bit driver within the Apps & features pane

If the 64-bit driver was installed, it will show 152 MB as the size of the driver.

The 64-bit driver within the Apps & features pane.

Step 5.  From ArcGIS Pro, connect to a folder on a local or network computer that contains the Excel files that you want to display within ArcGIS Pro.

Note: If ArcGIS Pro was opened while installing the driver, close and reopen ArcGIS Pro.

Add folder connection from the Catalog pane
Review folder contents from Catalog pane

Step 6.  Click your Excel (.xlsx) file to expand the workbook and display one or more worksheets.

TIP: When accessed from ArcGIS Pro, a worksheet is shown as a separate table with a dollar sign symbol ($) displayed at the end of the worksheet name. Spaces in worksheet names will be replaced by underscores.

Expanded Excel workbook displaying two worksheets, Rock$ and Soil$

Remember, when creating and maintaining data in Excel that you will use within ArcGIS Pro, typically some data preparation is necessary to ensure the data is correctly identified in ArcGIS Pro. Therefore, it’s important to be aware of limitations and follow these best practices for valuable tips when working with Excel data.

Check out the Work with Microsoft Excel and CSV files in ArcGIS Pro blog post to explore a step-by-step workflow for importing geological data maintained by a mining company in Microsoft Excel and CSV files for use in an ArcGIS Pro project.

Now that you can work with Excel files in ArcGIS Pro, an entirely new treasure trove of data has been opened for exploration.

What will you do with your newfound powers?

About the author

Elaine is a Product Engineer on the Geodatabase team and has worked with the geodatabase in various capacities at Esri since 2001. Elaine is passionate about finding opportunities to add valuable and creative content for the geodatabase technology that empowers ArcGIS users. A southerner born and bred, she's a mom, weather enthusiast, aficionado of fine teas, baking, bagpipe music and lover of all things pineapple.

Connect:

Leave a Reply

Please Login to comment

Next Article

What's new in ArcGIS Survey123 (October 2020)

Read this article