IMPORT DELIMITED command

Used to define ACL tables from delimited text files.

Syntax

IMPORT DELIMITED TO table import_filename FROM source_filename
<SERVER profile_name> 0 SEPARATOR char QUALIFIER char CONSECUTIVE
STARTLINE n <KEEPTITLE> <CRCLEAR> <LFCLEAR> [field_syntax] <...n> <IGNORE field_num> <...n>

 

field_syntax ::=
FIELD name type AT value DEC decimals WID view_width
PIC format AS display_name

Parameters

TO table

Specifies the name of the ACL table to import the data into.

import_filename

Specifies the name of the ACL data file. The import_filename parameter must be specified as a quoted string with a .FIL file extension (e.g., "Invoices.FIL").

If you do not want to create the .FIL file in the same directory as the ACL project, you can specify an absolute or relative file path to save the .FIL file to an existing folder.

For example: "C:\data\Invoices.FIL" or "data\Invoices.FIL"

FROM source_filename

Specifies the name of the source data file. This parameter must be specified as a quoted string.

If the source data file is not located in the same directory as the ACL project, you must use an absolute path (e.g., "C:\data\source_filename") or a relative path (e.g., "data\source_filename") to specify the file location.

SERVER profile_name

Optional. Specifies the server profile name for the ACL Server where the data you want to import is located.

SEPARATOR char

Specifies the separator between field values, also known as the delimiter. The char value must be enclosed in double quotation marks. For example: SEPARATOR ","

QUALIFIER char

Specifies the text qualifier. The char value must be enclosed in double quotation marks. For example: QUALIFIER "’"

To specify the double quotation mark character as the text qualifier, enclose the double quotation mark in single quotation marks. For example: QUALIFIER '”'

CONSECUTIVE

Specifies that consecutive text qualifiers should be treated as a single qualifier.

STARTLINE num

Specifies the line the data begins on. For example, if the first four lines of data contain header information that you do not want, specify that the data starts on line 5.

KEEPTITLE

Optional. Specifies that you want to use the values in the first row in the delimited text file as the field names in the table layout.

CRCLEAR

Optional. Specifies that the delimited file being imported is cleansed of any CR characters (carriage return) that occur between text qualifiers. If you use both the CRCLEAR and the LFCLEAR parameters, the CRCLEAR parameter must come first.

Specifying CRCLEAR requires that you also specify the QUALIFIER parameter.

LFCLEAR

Optional. Specifies that the delimited file being imported is cleansed of any LF characters (line feed) that occur between text qualifiers.

Specifying LFCLEAR requires that you also specify the QUALIFIER parameter.

FIELD name type

Specifies the name and data type of the field. For information on the values you can enter for the type parameter, see Identifiers for field data types.

AT value

Specifies the start position for the field in the data file.

DEC value

Specifies the number of decimals.

WID value

Specifies the length of the field in the ACL table layout.

PIC value

Specifies the format for date and numeric fields. For numeric fields the format specifies how the values are displayed. For date fields, the format specifies how dates are stored and read from the data source.

AS display_name

Specifies the display name (alternate column title) for the field in the ACL view. The AS parameter is required when you are defining a field. If you want the display name to be the same as the field name, you can enter a blank display_name value using the following syntax: AS "". Make sure there is no space between the two double quotation marks.

IGNORE field_num

Optional. Specifies that the field should not be included in the table layout. The field_num parameter specifies the position of the field in the source data. For example, IGNORE 5 excludes the fifth field in the source data from the ACL table layout.

Example

The following example imports data from a comma delimited file to an ACL table named Employees.

IMPORT DELIMITED TO Employees "Employees.fil" FROM "Employees.csv" 0
SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE
FIELD "First_Name" C AT 1 DEC 0 WID 11 PIC "" AS "First Name"
FIELD "Last_Name" C AT 12 DEC 0 WID 12 PIC "" AS "Last Name"
FIELD "CardNum" N AT 24 DEC 0 WID 16 PIC "" AS "Card Number"
FIELD "EmpNo" N AT 40 DEC 0 WID 6 PIC "" AS "Emp. Number"
FIELD "HireDate" C AT 46 DEC 0 WID 10 PIC "" AS "Start Date"
FIELD "Salary" N AT 56 DEC 0 WID 6 PIC "" AS "Salary"
FIELD "Bonus_2002" C AT 62 DEC 0 WID 10 PIC "" AS "Bonus"


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