Creating lookups and dropdown lists from an Excel Workbook

This page was originally published in 2014 and may contain obsolete information.

There are circumstances where you may want to load values to a dropdown menu or use them in a lookup from an Excel workbook.

The remainder of this article assumes the reader has good knowledge of Windows administration concepts. Support for this material is not covered under FileCare. This page contains links to external websites out of the control of FileHold. 

The feature needed to support this operation is called User Configured Database Server Connection. This allows you to tell the document management software to access the data with a method specified by the user. The document management software does not require any prior knowledge of this data access method, but it does require the user to have full knowledge of how to install and configure this method.

Using Excel as a data source in this manner is a very special case solution and the pros and cons should be carefully weighed before considering this for a production implementation. For example, it is best used when the data will never change and there is no other option. It is not suitable for FileHold Cloud customers.

In this example, we will create a managed imports lookup, but the principals here could apply equally to a dropdown menu field or a document schema database lookup used in the metadata panel. 

In order to use this feature, you will need a license with Custom Providers and Queries enabled. Upon request, a new license file can be sent to you at no charge. To request a license with this feature enabled, please email [email protected].

Preparing the Excel Workbook

For the purposes of this example, we will use the following Excel table. We are using an Excel table for convenience of editing and maintenance in Excel; this is not a requirement of FileHold. The first row has the names of the columns. This will help us to relate the data in the configuration dialog. We have created two columns; one is simply a unique ID and the other is the actual information we want in our dropdown menu. This is not a mandatory requirement of FileHold, but it is highly recommended, and it will make long term management of the list much less prone to problems.

We have only a single worksheet in our workbook, but you could have many worksheets. Each one will appear as a separate table in the configuration dialog.

Image
Sample Microsoft Excel spreadsheet for lookups

Preparing the data provider

The metadata field configuration will expect us to provide a dot net compatible data provider. The data provider is the technology that knows how to read data from the data source. For this example the data source is an Excel workbook file. The data provider included with Microsoft Access 2010 knows how to read Excel files and it is free.

If you do not have Microsoft Access 2010 installed on the document management software server you will need to download and install the Microsoft Access Database Engine 2010 Redistributable (link is external) from Microsoft. These drivers can read a number of database and file formats including Access, text, and Excel.

If you are interested to know what OLEDB providers are available in your system you can run the Data Link Properties dialog. Methods for starting this dialog vary depending on your operating system version. Perhaps the easiest method is to create an empty text file with the extension UDL. Simply double click on this file and this dialog will appear.

Image
Data link properties

Setting up the lookup

  1. To start, go to the lookup tab on your document schema configuration and put a check in the Enable Schema Lookup box.
  2. Select the User Configured Database Server Connection radio button and select the .Net Framework Data Provider for OleDb data provider.
Image
Schema lookup configuration with Excel spreadsheet
  1. The connection string must be entered before the connection can be verified. This connection string is specific to each OLEDB data provider. Documentation will typically be provided with the vendor’s driver. For the Microsoft Access Database Engine 2010 with an Excel 2010 workbook file located at C:\Integration\ClientList.xlsx the connection string should be specified as follows:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Integration\ClientList.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

A good source for information on the correct connection strings is available at www.connectionstrings.com.

  1. Press the Verify Connection button to confirm that you have entered the parameters and setup the environment correctly. You can select the database; the only possible value will be ---- as there is no actual database in this scenario.
  2. At this stage the remainder of the setup will be as for any database lookup.

If you receive an error when configuring a lookup from an Excel file you should confirm that the FileHold service user has permission to the file, the file exists as specified in the connection string, and the file is not opened by another application.

Linking the schema lookup and a manage import profile

The final step is to connect the schema lookup with the manage import profile. The manage import profile can perform database lookups from the client or the server. Custom providers are only available to manage imports using the server lookup.

  1. From the manage import profile configuration dialog, select the document schema that you configured above on the field mapping tab.
  2. From the database lookup tab check the server lookup box. There are no further options to choose. The schema lookup you previously defined will contain all the information necessary for manage imports and your database lookup fields will now be available for field mapping.
Image
Manage import configuration with Excel spreadsheet