Creating dropdown lists and lookups from a text file

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 list from a text. This file could be data exported from another system or created manually. For this example we will use a CSV formatted text file.

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 a text file 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 dropdown menu field, but the principals here could apply equally to a document schema database lookup used in the metadata panel or with managed imports.

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 text file

To start, we need a text file for our example. The file will be a list of client IDs and client names. The format will be comma-separated values. Other variations are possible, but this format is a common one. The file will simulate a database table with two columns. We will keep it in a folder called C:\DropdownValues in a file called Clients.csv. We could keep many similar files in this folder. Each one would appear as a different table in the document management software.

The first line in the file will specify the names of the columns. The next five rows will be the data as shown below.

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 the example the data source is a text file, so we will use the data provider included with Microsoft Access 2010. It knows how to read text 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 from Microsoft. These drivers can read a number of database and file formats including text, Excel, and Access.

Additional information

The documentation from Microsoft is not very complete, but there is some useful information related to reading text files at the following links:

Read Text File (txt, csv, log, tab, fixed length)

Schema.ini File (Text File Driver)

The schema.ini file method is only required for fixed format text files and other more complex settings.

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

Setting up the metadata field

1. To start, create a metadata field as normal with the Drop Down Menu – Database Lookup type. In this example our field will be called Client list. Switch to the Field Properties tab.
2. Select the User Configured Database Server Connection radio button and select the .Net Framework Data Provider for OleDb data provider.

Image

3. 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 the text file(s) located in the C:\DropdownValues folder, where the files are in the comma separated format with a header row, the connection string should be specified as follows:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DropdownValues;Extended Properties="Text;Header=Yes;Format=​CSVDelimited"

4. 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.

Image

5. At this stage the setup is the same as for any database field setup. Note that if there is more than one text file in the C:\DropdownValues folder, each will be displayed as a table with the period in the filename replaced by a hash symbol (#). If you want to use a custom SQL query you will need to specify the table name in the same way such that ClientNames.csv will be ClientNames#csv.

Image

As for any database lookup field, the source data is copied to the document management software database once per hour or when a new field is configured. When users are accessing the data in real time, their data is coming from the copy in the document management software database, not from the source data.