Guidelines for defining delimited text files

Delimited text files are commonly used to import and export data from spreadsheet and database applications. Delimited text files represent each database or spreadsheet row as a row in the delimited text file. In a delimited text file, each record is 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 are separated by a field separator character. There are three main types of delimited files that are categorized based on the field separator character they use:

If the field separator character is used in the data, a text qualifier (typically double-quotes (" ") or single- quotes (' ')) is used to indicate that the character is a part of the value, and not a field separator. If you use a comma as your field separator, values that use commas need to be qualified with quotes. For example, $1,000 would be interpreted as two fields ($1 and 000) unless the field uses a text qualifier ("$1,000").

After you define an ACL table from a delimited text file, you can update the table whenever necessary by right-clicking the table in the Overview tab in the Project Navigator and selecting Refresh from Source. The command used to define the table is re-run, so if the structure of the data in the data source remains the same the field definitions in the table layout still apply. If you update a table where any of the field values in the data source are longer than field length the values will be truncated. The field length in the data source is set based on the longest value in the field, or the field name if it is longer. You can modify this default value by editing the field length in the table layout. If anything more extensive than minor changes to field lengths have been made to the source data structure, you should define the table again instead of refreshing the data.

Example

The following example shows the first four records from a delimited text file. The file uses commas as the field separator and double-quotes 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 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) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback