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 Microsoft Excel files. This article answers the following four questions about using Excel files in ArcGIS Pro:
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?
1. Do I need to have Microsoft products installed to connect to an Excel spreadsheet?
The short answer is no. Excel tables may be used directly in ArcGIS Pro. Neither Excel nor Microsoft Office have to 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 generate the ArcGIS Pro error message “Required Microsoft driver is not installed.”
2. How do I know which Microsoft Access Database driver to install?
To answer this question, you need to first understand and verify a few things. First, why do you need to install the Microsoft Access Database Engine driver? 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:
Microsoft Access Database Engine 2016 Redistributable 32-bit driver
Microsoft Access Database Engine 2016 Redistributable 64-bit driver
Prior to installing the driver, you need to know if any Microsoft products are already installed on your machine and if these products have been installed using either the Click-to-Run (CTR) technology or a Microsoft Installer (MSI).
Microsoft Office is installed using either MSI or CTR. MSI (more recently referred to as a Windows Installer file) is typically one large download file that contains both the installer and all Office components. CTR, an alternative to the traditional MSI installer method, 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 (32-bit driver) on a machine that already has a Microsoft 64-bit application, you will get an error message saying you can’t install it 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 encounter either of these issues, don’t despair. There’s a simple workaround, which involves performing a silent installation of the downloaded Microsoft Access Database Engine 2016 driver.
Now that you have identified the appropriate driver for your configuration, let’s verify three key points before you download that driver.
Point 1
Are any Microsoft Office products currently installed on the client machine on which ArcGIS Pro is installed?
If the answer is no, skip down to 3. How do I know if a silent install is needed?
If the answer is yes, proceed to Point 2 below.
Point 2
How do you tell whether your Microsoft Office install is MSI or CTR?
Note: These instructions are applicable for any Office app (e.g., Word, Excel), but this example uses Word.
In Word, Go to File > Account. On the right side, under Product Information, in the About Word section, look at the end of the line containing the version and build numbers. If the CRC installer used, Click-to-Run will be displayed. If there is nothing after the build number, then it’s an MSI install.
Point 3
Which Microsoft Office bit level is installed, 32 bit or 64 bit?
It’s easy to tell if the bit level of your installation is 32 bit or 64 bit. These instructions are applicable for any Office app (e.g., Word, Excel), but this example uses Word. In Word, Go to File > Account and click About Word. This will open the About Microsoft Word window. The top line contains the combined version and build number followed by either 64-bit or 32-bit.
3. How do I know if a silent install is needed?
Based on your answers to the previous questions, use the decision tree in Figure 1 to help you determine the appropriate Microsoft Access Database driver to install and determine if a silent install is needed.
If you do not need a silent install, use these instructions.
If you do need a silent install, use the steps under 4. How do I complete a silent install of the Microsoft Access Database Engine driver?
Important: If you are not sure whether a silent install is needed or if you have both 32-bit and 64-bit Microsoft products installed, download the Microsoft Access Database Engine 2016 Redistributable 64-bit driver (AccessDatabaseEngine_X64) from https://bit.ly/3sqSTVQ . To complete a silent install of this driver, follow these steps outlined under 4. How do I complete a silent install of the Microsoft Access Database Engine driver?
4. How do I complete a silent install of the Microsoft Access Database Engine driver?
Before beginning the following steps to complete a silent install of the Microsoft Access Database Engine driver, ensure that ArcGIS Pro is closed.
Step 1
- To download the Microsoft Access Database Engine 2016 Redistributable driver, go to https://bit.ly/3sqSTVQ.
- Check the box next to accessdatabaseengine.exe, if your Office app is 32 bit. Check the box next to accessdatabaseengine._X64.exe,
if your Office app is 64 bit. - Click Next to begin the download.
- On the local machine where ArcGIS Pro resides, create a new folder named MS_Driver off the root directory of the drive. Example: This PC > OSDisk (C:) > MS_Driver.
Step 2
In the Windows taskbar Search box, type cmd into the search box. You will see Command Prompt appear in the Search window. Click the Run as administrator option displayed on the right side of the Search window.
Step 3
From the Command Prompt, provide the full path of the Microsoft Access Database Engine driver .exe file stored locally on your machine and add /quiet. Hit the Enter key to run this as a silent install.
In this example, the full path of the 32-bit driver .exe file run as a silent install would be
C:\MS_Driver\accessdatabaseengine.exe /quiet
In this example, the full path of the 64-bit driver .exe file run as a silent install would be
C:\MS_Driver\accessdatabaseengine_X64.exe /quiet
Step 4
To verify that the driver was installed successfully, in the taskbar search box, type Add or remove programs and select Add or remove programs System settings from the search results. In the Apps & features pane, search for Microsoft Access database engine 2016 to locate the driver and verify that it has been installed.
Step 5
Open ArcGIS Pro. From the Catalog pane, click and expand Folders. Right-click Folders and select Add Folder Connection. Browse, type, or copy the full path to the folder on your local or network computer that contains the Excel files you want to display in ArcGIS Pro. Click OK. The folder connection appears in the Catalog pane in the Folders category.
Step 6
Click an 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 ($) displayed at the end of the worksheet name. Spaces in worksheet names will be replaced by underscores.
Some Final Notes
Remember, when creating and maintaining data in Excel that you will use in ArcGIS Pro, some data preparation is typically necessary to ensure that data is correctly identified by ArcGIS Pro. Read the documentation at https://bit.ly/3C12LZu to learn the limitations of this data source and https://bit.ly/3C4djY4 to learn best practices when using data in Excel files. Now that you can work with Excel files in ArcGIS Pro, an entirely new treasure trove of data has been opened to you. What will you do with your newfound powers?
About the author
