Summary

If you configure Quick Fields 8's Lookup or Field Lookup process to return a value from a date-formatted column in a Microsoft Excel file, it may return both the date (which is visible when viewing the Excel file) and a time stamp (which is not visible in Excel). For example, the processes may return 1/1/2010 12:00:00 AM, even though the Excel file appears to only contain 1/1/2010 in the cell.

Cause

When returning data to Quick Fields, Excel may automatically append a time stamp to date values.

Resolutions

Resolution 1: Use a Different Data Source
Though Excel is supported, we do not recommend using it as a data source in a production environment. In addition to the issue explained in this article, Excel has other limitations, including its design for data type support, file size, requirement that the file must be opened every time a client needs to read from it, and its 65,535 row limit.

Resolution 2: Use Inline Regular Expressions
If you have purchased the Pattern Matching add-on (required for this resolution), follow the instructions below to use inline regular expressions to remove the time stamp from the token value. These instructions do not apply to the configuration of the lookup process, and should be performed after the lookup process is configured.

1. After adding the lookup token to a text box (e.g., a field or document name box) in the Tasks Pane, right-click anywhere in the box and select Token Editor.
2. Select Include Regular Expression.
3. Enter a regular expression that will extract only the date from the value returned by the data source.

   Example: If the data source will return 1/1/2010 12:00:00 AM, and you only want to use 1/1/2010 to populate a field, use the following regular expression: (.+)\s+\d+:\d+:\d+\s+[AP]M. More info on using the Token Editor.

4. Click OK. Continue to configure the session, as necessary.