Import a delimited text file to Analytics for analysis using a variety of different tools.
How it works
You use the Data Definition Wizard to select one or more delimited text files and import the data to Analytics. The imported data creates one or more new Analytics tables and associated data files (.fil). Each imported delimited text file creates a separate Analytics table.
The Analytics data file contains a copy of the delimited data that is completely separate from the original delimited text file.
You can 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.
Import a single file or multiple files
You have the option of importing a single delimited text file or multiple files in a single operation. The import process differs somewhat, depending on which option you use:
- single file you have the option of manually defining both file-level and field-level properties during the import process
- multiple files you can manually define only file-level properties during the import process. Analytics automatically defines field-level properties and no manual definition is possible during the import
For example, during the import of multiple files, you cannot:
- specify the data type of fields
- selectively exclude fields from the import
Once the data is in Analytics, you can make any necessary adjustments to the data definition in the Table Layout dialog box.
Combine multiple files
After you import multiple delimited text files into individual Analytics tables you might want to combine them into a single Analytics table. For example, you could combine the data from twelve monthly tables into a single annual table containing all the data. You can combine the files only after you have imported them into individual Analytics tables.
For information about combining multiple Analytics tables, see Appending tables.
Tip
To reduce labor, try combining the multiple tables first before making any required adjustments to the data definition in the new combined table.
The structure of delimited text files
Delimited text files typically have a .txt or .csv file extension, although other file extensions are possible. 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 – 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).
Example of a delimited text file
The example below shows the first four rows in a delimited text file.
- The first row contains the field names.
- The field separator is a comma. Each row includes seven fields separated by commas.
- The text qualifiers are double quotation marks. 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"
How overwriting works
If you import delimited data and create a new Analytics table that has the same name as an existing table in the Analytics project you overwrite the existing table.
Show me more
Both parts of a table overwritten
Overwriting of Analytics tables is complicated by the fact that both parts of a table can be overwritten:
- the table layout, displayed in the Navigator
- the associated source data file, stored in a Windows folder
(For information about table layouts and source data files, see The structure of Analytics tables.)
The two parts of the table are overwritten independently of each other. If both parts have the same name as the new table, both are overwritten. This scenario is the most common.
But if the table layout and the source data file have different names, only the one with the same name as the new table is overwritten.
This overwrite behavior applies whether you are importing a single delimited text file, or multiple files.
Overwriting when importing multiple delimited text files
When you import multiple delimited text files, overwriting also depends on both the Overwrite existing tables and Output Path settings.
The sections below summarize the different possible outcomes of overwriting when importing multiple delimited text files, starting with the most common scenario.
Same name: new table, existing table layout, existing source data file
|
Same "Output Path" as existing source data file |
Different "Output Path" from existing source data file |
|
|---|---|---|
| "Overwrite existing tables" selected |
|
|
| "Overwrite existing tables" not selected |
|
|
Different name: existing table layout
|
Same "Output Path" as existing source data file |
Different "Output Path" from existing source data file |
|
|---|---|---|
| "Overwrite existing tables" selected |
|
|
| "Overwrite existing tables" not selected |
|
|
Different name: existing source data file
|
Same "Output Path" as existing source data file |
Different "Output Path" from existing source data file |
|
|---|---|---|
| "Overwrite existing tables" selected |
|
|
| "Overwrite existing tables" not selected |
|
|
Import a single delimited text file
Import a single delimited text file to create a new Analytics table. You have the option of manually defining both file-level and field-level properties during the import process.
Show me how
Locate and select the delimited file
- Select Import > File.
- In the Select File to Define dialog box,
locate and select the delimited text file and click Open.
Delimited text files can have several different file extensions, including .txt and .csv.
Specify the delimited file properties
- In the Character Set page, verify that the correct character set option is selected and click Next.
- In the File Format page, verify that Delimited text file is selected and click Next.
- 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.
Note
Maximum field length is 32,767 characters (non-Unicode edition) or 16,383 characters (Unicode edition). The entire field length, up to the maximum, is imported into Analytics, but only the first 256 characters are displayed in the table view. The remainder of the data is present, and can be analyzed, but it is not visible in the view. To see all the data, open the Table Layout dialog box.
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, “Diligent Corporation”” (terminating with two double quotation marks) is equivalent to “Diligent Corporation” 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.
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 Analytics 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 Analytics, or enter a different name. |
| Column Title | The column
title for the field in the default Analytics 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 the resulting Analytics table. You can keep the data type assigned by Analytics, or select an appropriate data type from the drop-down list. For information about the supported data types in Analytics, see Data types in Analytics. |
| 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
- 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.
- 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.
Import multiple delimited text files
In a single operation, import multiple delimited text files. Once imported to an Analytics project, each delimited file becomes a separate Analytics table.
You can manually define only file-level properties during the import process. Analytics automatically defines field-level properties and no manual definition is possible during the import.
Once the data is in Analytics, you can make any necessary adjustments to the data definition in the Table Layout dialog box.
Show me how
Note
All first rows in the files that you import should use a consistent approach. First rows should be either field names, or data, across all files. Avoid mixing the two approaches in a single import operation.
If the files have an inconsistent approach to first rows, use two separate import operations.
Locate and select the delimited files
- Select Import > File.
- In the Select File to Define dialog box,
locate and select the delimited text files and click Open.
Delimited text files with the following file extensions are supported: .txt, .csv, .del, .dat
You can Shift+click to select multiple adjacent files, or Ctrl+click to select multiple non-adjacent files.
Make the initial import preparations
- In the Delimited File Properties page, select the files to import.
Keep the files selected by default, or deselect any files you do not want to import. Select the first checkbox if you want to deselect or select all the files.
- Review the settings assigned by Analytics, make any required updates, and click Next.
Setting Description Table Name The name for the table in the Analytics project.
Keep the name assigned by Analytics, or double-click a table name, type a different name, and press Enter.
Note
The table name applies to both the new table layout and the new source data file created when importing the data.
Overwrite existing tables Existing tables with identical names in the Analytics project are overwritten.
For detailed information, see How overwriting works.
Output Path Specifies the folder where the new Analytics data files (.fil) are saved.
If you leave Output Path blank, the Analytics data files are saved in the folder containing the Analytics project.
- If the error message "Existing file or table names detected" appears, click OK and do one or both of the following:
- Select Overwrite existing tables if any existing table layouts or associated data files with identical names can be overwritten.
- In the Table Name setting, rename imported tables as required to avoid overwriting any existing table layouts or associated data files.
-
In the confirmation dialog box, click Yes to continue, or No to go back and change the selection of files.
Specify the delimited file properties
Note
The properties you specify apply to all files being imported. If the files are inconsistently structured, the properties will not be accurate for all files and there could be problems with the import.
- 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 files.
This setting allows you to skip lines at the beginning of files that contain information you do not want to import. For example, if the first three lines of each 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.
Note
Maximum field length is 32,767 characters (non-Unicode edition) or 16,383 characters (Unicode edition). The entire field length, up to the maximum, is imported into Analytics, but only the first 256 characters are displayed in the table view. The remainder of the data is present, and can be analyzed, but it is not visible in the view. To see all the data, open the Table Layout dialog box.
Tip
If you intended to periodically refresh a 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 each file are used as field names in the resulting table layouts.
Note
If you use this setting, the row used as field names is whatever line number is specified in Start on Line.
This setting applies globally to all files that you import.
Treat Consecutive qualifiers as one Duplicate qualifier characters are ignored.
For example, “Diligent Corporation”” (terminating with two double quotation marks) is equivalent to “Diligent Corporation” if you select this option.
Field Separator The character that separates fields in the files:
- 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.
Finalize the import
In the Final page, verify the settings for the new Analytics tables and click Finish.
If you want to make any changes, click Back to get to the appropriate page in the wizard.
The new Analytics tables are created with data from the imported files.
