Import a delimited text file

You can create an Analytics 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 Analytics Server.

The structure of delimited text files

Delimited text files are often used to import data from spreadsheet or database applications into Analytics. Each spreadsheet or database row becomes a row in the delimited text file, with each row or record separated by a line separator. The valid line separators are:

• CR carriage-return
• LF line-feed
• CRLF carriage-return line-feed (the standard DOS/Windows character sequence)

Field separator character

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:

• Comma separated values (.csv) – Commas are used to delimit the fields in each record.
• Tab separated values (.tsv) – Tabs are used to delimit the fields in each record.
• Text files (.txt) – Commas, tabs, or another field separator character are used to delimit the fields in each record. Other common field separator characters are spaces, pipes (|), and semicolons (;).

Text qualifier character

If a field separator character is used, a text qualifier character is also used to enclose character 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).

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"

Locate and select the delimited text file

Use the method that matches your installation of Analytics.

Standalone installations of Analytics

1. Select File > New > Table.
2. In the Select Data Source page, make sure File 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 Analytics table from and click Open.

Analytics integrated with Analytics Exchange

1. Select File > New > Table.
2. In the Select Platform for Data Source page, do one of the following:
   • To use Analytics to access data, select Local and click Next.

   • To use an Analytics Server to access data, select ACL Server and select a Windows server profile from the drop-down list and click Next.

3. If you selected Local:
   1. In the Select Local Data Source page, make sure File 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 Analytics 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 Analytics 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 length of the field in the resulting table layout. Specify the length in characters. You can keep the length assigned by Analytics, or enter a different length. Tip If you intended to periodically refresh the resulting Analytics table from updated source data, or re-use the import command, enter a longer field length than the one assigned by Analytics. 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 Ltd. dba Galvanize”” (terminating with two double quotation marks) is equivalent to “ACL Services Ltd. dba Galvanize” 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 Analytics, 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 Analytics 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 Analytics data file

In the Save Data File As dialog box, enter a name for the Analytics data file and click Save.

If Analytics prefills a data file name, you can accept the prefilled name, or change it.

You can also navigate to a different folder to save the data file if you do not want to use the default location opened by Analytics.

Edit the Analytics field properties

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

Finalize the import

1. In the Final page, verify the settings for the new Analytics 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 table layout that you are adding to the project, or keep the default name, and click OK.

   The new Analytics table is created with data from the imported file.