Guidelines for defining delimited text files

Delimited text files are often used to import data from spreadsheet or database applications into ACL. Each database or spreadsheet row becomes a row in the delimited text file, with each row or record separated by a line separator. The valid line separators are carriage-return (CR), line-feed (LF), and the standard DOS/Windows character sequence CRLF (both).

Fields in each record in the delimited text file are separated by a field separator character. There are three main types of delimited text file, based on the field separator character they use:

If a field separator character is used, a text qualifier character is also used to enclose field values and isolate them from field separators. Common text qualifier characters are double (" ") or single (' ') quotation marks. For example, if a comma is the field separator character, enclosing the value $1,000 in text qualifiers ("$1,000") ensures that the value is interpreted as a single value and not as two values ($1 and 000).

After you define an ACL table from a delimited text file, you can update the ACL table whenever necessary by right-clicking the table in the Overview tab in the Navigator and selecting Refresh from Source. The command used to originally define the table is re-run, and any new records or changes in the source table are added to the ACL table. As long as the structure of the data in the source table remains the same the field definitions in the ACL table layout still apply.

If any of the new field values in the source table are longer than the specified field lengths in the ACL table layout, the values are truncated in the ACL table. To acquire the full values, you need to define the table again instead of refreshing the data.

Example

The following example shows the first four records in a delimited text file. The file uses commas as the field separator and double quotation marks as the text qualifier. Each row includes seven fields separated by commas. The first row identifies the field names in the table. The last field includes a text qualifier, so that the comma in the dollar value is not interpreted as a field separator.

First_Name,Last_Name,CardNum,EmpNo,HireDate,Salary,Bonus_2011
Lila,Remlawi,8590122497663807,000008,12/28/2007,52750,"$1,405.40"
Vladimir,Alexov,8590122281964011,000060,10/5/2007,41250,"$4,557.43"
Alex,Williams,8590124253621744,000104,8/12/2010,40175,"$7,460.02"


(C) 2015 ACL Services Ltd. All Rights Reserved.