Defining and importing data > Data Definition Wizard > Define a delimited text file

Define a delimited text file

You can create an ACL table by defining and importing a delimited text file. Delimited text files typically have a .txt, .csv (comma separated), or .tsv (tab separated) file extension.

You can define and import delimited text files located on your local computer or on a network drive. Users of Analytics Exchange can also access delimited text files located on an ACL Server.

Locate and select the delimited text file

Use the method that matches your installation of ACL.

Standalone installations of ACL

  1. Select File > New > Table.
  2. In the Select Data Source page, make sure Disk is selected and click Next.
  3. In the Select File to Define dialog box, locate and select the delimited text file you want to create the ACL table from and click Open.

ACL integrated with Analytics Exchange

  1. Select File > New > Table.
  2. In the Select Platform for Data Source page, do one of the following:
  3. If you selected Local:
    1. In the Select Local Data Source page, make sure Disk is selected.
    2. Click Next.
  4. If you selected ACL Server:
    1. Enter your server profile password if prompted.
    2. In the Select ACL Server Data Source page, make sure Flat Files is selected.
    3. Click Next.
  5. In the Select File to Define dialog box, locate and select the delimited text file you want to create the ACL table from and click Open.

Specify the delimited file properties

  1. In the Character Set page, verify that the correct character set option is selected and click Next.
  2. In the File Format page, verify that Delimited text file is selected and click Next.
  3. In the Delimited File Properties page, review the settings assigned by ACL to the properties listed below, make any required updates, and click Next.
    Property Description
    Start on Line The line number on which to start reading the file.

    This setting allows you to skip lines at the beginning of a file that contain information you do not want to import. For example, if the first three lines of a file contain header information, enter 4 to start reading data on the fourth line.

    Field Width For the selected column heading in the preview table, specifies the field length in the resulting table layout.

    You can keep the length assigned by ACL, or enter a different length.

    Tip

    If you intended to periodically refresh the resulting ACL table from updated source data, or re-use the import command, enter a longer field length than the one assigned by ACL.

    A longer field length provides extra space if updated values in the source data are longer than any of the current values. Values that exceed the available field length are truncated.

    Use first row as field names Values in the first line in the file are used as field names in the resulting table layout.

    Note

    If you use this setting, the row used as field names is whatever line number is specified in Start on Line. If the field names are not correct, you can update them in a subsequent page in the Data Definition Wizard.

    Treat Consecutive qualifiers as one Duplicate qualifier characters are ignored.

    For example, “ACL Services”” (terminating with two double quotation marks) is equivalent to “ACL Services” if you select this option.

    Field Separator The character that separates fields in the file:
    • Comma
    • TAB
    • Semicolon
    • Other – allows you to specify the character that is used as the field separator
    Text Qualifier The text symbol that identifies values contained in fields:
    • Double Quote
    • Single Quote
    • None – indicates that no text qualifier is used
    • Other – allows you to specify the character that is used as the text qualifier
    Clear CR and Clear LF Cleanses the imported data of misplaced carriage return (CR) and/or line feed (LF) characters.

    Misplaced CR/LF characters can cause incorrect line breaks within records. When enabled, the option replaces any CR/LF characters with a space. Only CR/LF characters that occur inside a pair of text qualifiers are replaced.

    For Windows files, select both Clear CR and Clear LF.

    The two options are disabled if Text Qualifier is None.

    All Character Assigns the Character data type to all the imported fields.

    Tip

    Assigning the Character data type to all the imported fields simplifies the process of importing delimited text files.

    Once the data is in ACL, you can assign different data types, such as Numeric or Datetime, to the fields, and specify format details.

    The All Character option is useful if you are importing a table with identifier fields automatically assigned the Numeric data type by ACL when in fact they should use the Character data type.

    Replace NULLs Cleanses the imported data of misplaced NUL characters.

    Misplaced NUL characters can cause gaps and incorrect field divisions within records. When enabled, the option replaces any NUL characters with a space.

Save the ACL data file

In the Save Data File As dialog box, enter a name for the ACL data file and click Save. If necessary, you can modify the location where the file will be saved.

Edit the ACL field properties

In the Edit Field Properties page, review the settings assigned by ACL to the properties listed below, make any required updates, and click Next.

Note

Select a column heading in the preview table to see the properties associated with the column.

Property Description
Ignore this field Excludes the field from the resulting table layout.

The data in the field is still imported, but it is undefined, and does not appear in the new ACL table. It can be defined later, if necessary, and added to the table.

Name The name for the field in the table layout.

You can keep the name assigned by ACL, or enter a different name.

Column Title The column title for the field in the default ACL view.

If you do not specify a column title, the Name value is used.

Note

If you selected All Character in the Delimited File Properties page, the options below do not apply and are disabled.

Type The data type assigned to the field in ACL.

You can keep the data type assigned by ACL, or select an appropriate data type from the drop-down list.

For information about the supported data types in ACL, see ACL data types.

Value A read-only property that displays the first value in the field.

The value dynamically updates based on any edits you make.

Decimal Numeric fields only. The number of decimal places in the source data.

Note

The Decimal text box appears automatically when you select a Numeric data type.

Input Format Datetime fields only. The format of datetime values in the source data.

The format you specify must exactly match the format in the source data.

For more information about date and time formats, see Formats of date and time source data.

Finalize the import

  1. In the Final page, verify the settings for the new ACL table and click Finish.

    If you want to make any changes, click Back to get to the appropriate page in the wizard.

  2. Enter a name for the ACL table you are adding to the project, or keep the default name, and click OK.

    The new ACL table is created with data from the delimited text file.