Crystal Reports: Text File Lookup via a User Function Library

The domain description lookup formula we developed in an example in another article was several hundred lines in length. Some domains might not be in the list now, but they could be in future data.

If we need this formula in multiple reports, the entire list needs to be copied into every formula in every report. The domain descriptions don’t change very often, but what if you had a description list you needed that did require frequent revision? Ideally, these things should be in your database--but sometimes, this isn’t possible. 

We’ve developed a User Function Library that can open and search through a text file for a line and retrieve a value from that file.  It is very useful if you have a CSV or Tab separated file, because the library includes functions that extract an individual column from a row of data. 

The complex and lengthy formula statement in the previous example is reduced to a simple text file lookup with

                TextLookupColumn ("domain.txt",{@TLD}, 1) 

Try the free trial of this text file lookup function.

With this ULF, you can search texts and return individual lines or columns. Our Text Lookup User Function Library is a set of seven functions that enable you to search through a text file and extract lines and columns to display in your report. It allows you to search text documents and return individual lines or columns. Free 30-day trial.

The Text Lookup User Function Library is a set of seven functions enables you to search through a text file and extract lines and columns to display in your report. It allows you to search text documents and return individual lines or columns.

For example, you may not have a database table with descriptions of your codes. By creating a text file with those codes and descriptions, this UFL will read the text file like a table. This avoids your needing to have repeated if..then ..else in multiple formulas.

This UFL supports multiple text files.

This User Function Library installs seven new functions in the Additional Functions list inside Crystal Reports.

1. TextLookupLine( FileName, Search). Open and search FileName for the Search text. The function returns the entire line where the search was found. If the Search is not in the file, an empty string is returned.
    

2. TextLookupColumn( FileName, Search, Col). Open and search FileName. This function assumes that the file is a series of values on a line with a tab or comma to separate the values into columns. The Search is made on the first column (which is regarded as column 0). The values are viewed as additional columns. If a column number of less than 0 or beyond the end of the line is requested a blank value is returned. If the search is not found a blank value is returned.
    

3. TextLookupColumnCount( FileName, Row). Open and search FileName for a specific row (or line). Count the columns in that line and return the number of columns. The values on the line can be tab or comma delimited.
    

4. TextLookupColumnSeperator( FileName). Open and search FileName. Examine the columns in that line and whether it is tab or comma delimited. The function will return Commas or Tabs and is intended for diagnostic use.
    

5. TextLookupLineNumber( FileName, LineNo). Open and search FileName and return a specific line number from the file. If the line number is beyond the end of the file, a blank is returned.
    

6. TextLookupCount( FileName). Open and search FileName and return the number of lines in the file.
    

7. TextLookupConfirm( FileName). Look for FileName and identify if the file is found or not. This is intended for diagnostic purposes.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.