Import data from Excel

Note: For information on overriding default configuration settings, refer to Modify configuration settings.

You can import data in an Excel spreadsheet to Advanced Database Editor (ADE).

About this task

Prior to importing an Excel spreadsheet to ADE, the following must be noted:

• The spreadsheet name must match the ADE table to which you are importing data.

• The column names between the Excel spreadsheet and ADE Table Edit dialog box must match.

• The order of the columns does not have to match.

To import data from Excel

1. Open the RealTime database (RTDB) table to which you want to import information.

   

2. Select Table > Import from Excel.

   The Open dialog box appears.

   

3. Select the file type from the dropdown menu.

   Note: The default option for Excel Import is the xlsx format. The xls format is supported for backward compatibility.

4. Select the Excel file that you want to import and select Open.

   The information from the spreadsheet appears in the Table Edit dialog box.

   During the Excel file import process, ADE checks if Microsoft Access Database Engine is currently installed on your computer. If Microsoft Database Engine is not installed, ADE prompts you to install it. To import and export files compatible with the Office 2016 or later version of Excel, AVEVA recommends downloading the latest version of Microsoft Access Database Engine (navigate to https://support.microsoft.com/en-us/office/download-and-install-microsoft-365-access-runtime-185c5a32-8ba9-491e-ac76-91cbe3ea09c9). Ensure that you download the 64-bit version.

   If you want to change the default version for Microsoft Database Engine and replace it with the Microsoft Access Runtime version that is installed on your computer, proceed as follows:

   1. In the %DNADataRoot%\ADE\config\Settings directory, either create or open the ADE_Override.json file.

   2. Add the following object:

      {

      "ADE": {

      "MS_ACCESS_DATABASE_ENGINE_DISPLAY_NAME": "Microsoft Access Runtime"

      }

      }

      Note: The display name in the JSON file should match the one installed on your computer. Each version has a different name, depending on the language pack installed. You can find the name by navigating to Control Panel > Programs > Programs and Features. Search for the word "access" to find the display name.

   3. Restart ADE for the changes to take effect.

5. Select Table > Save Changes.

   Note: You must have the correct permissions to save this information to the database.