IMPORT DELIMITED command
Concept Information
Creates an Analytics table by defining and importing a delimited text file.
Syntax
IMPORT DELIMITED TO table import_filename FROM source_filename <SERVER profile_name> source_char_encoding SEPARATOR {char|TAB|SPACE} QUALIFIER {char|NONE} <CONSECUTIVE> STARTLINE line_number <KEEPTITLE> <CRCLEAR> <LFCLEAR> <REPLACENULL> <ALLCHAR> {ALLFIELDS|[field_syntax] <...n> <IGNORE field_num> <...n>}
field_syntax ::=
FIELD name type AT start_position DEC value WID bytes PIC format AS display_name
Parameters
Name | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
TO table |
The name of the Analytics table to import the data into. Note Table names are limited to 64 alphanumeric characters. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number. |
|||||||||||||||
import_filename |
The name of the Analytics data file to create. Specify import_filename as a quoted string with a .FIL file extension. For example, "Invoices.FIL". By default, the data file (.FIL) is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the data file to a different, existing folder:
|
|||||||||||||||
FROM source_filename |
The name of the source data file. source_filename must be a quoted string. If the source data file is not located in the same directory as the Analytics project, you must use an absolute path or a relative path to specify the file location:
|
|||||||||||||||
SERVER profile_name optional |
The server profile name for the AX Server where the data you want to import is located. | |||||||||||||||
source_char_encoding |
The character set and encoding of the source data. Depending on which edition of Analytics you are using, and the encoding of the source data, specify the appropriate code:
|
|||||||||||||||
SEPARATOR char | TAB | SPACE |
The separator character (delimiter) used between fields in the source data. You must specify the character as a quoted string. You can specify a tab or a space separator by typing the character between double quotation marks, or by using a keyword:
|
|||||||||||||||
QUALIFIER char | NONE |
The text qualifier character used in the source data to wrap and identify field values. You must specify the character as a quoted string. To specify the double quotation mark character as the text qualifier, enclose the character in single quotation marks: QUALIFIER '"'. You can specify that there are no text qualifiers using either of these methods:
|
|||||||||||||||
CONSECUTIVE optional |
Consecutive text qualifiers are treated as a single qualifier. |
|||||||||||||||
STARTLINE line_number |
The line number on which to start reading the file. For example, if the first three lines of a file contain header information that you do not want, specify STARTLINE 4 to start reading data on the fourth line. |
|||||||||||||||
KEEPTITLE optional |
|
|||||||||||||||
CRCLEAR optional |
Replaces any CR characters (carriage return) that occur between text qualifiers with space characters. You must specify QUALIFIER with a char value to use CRCLEAR. If you use both CRCLEAR and LFCLEAR, CRCLEAR must come first. |
|||||||||||||||
LFCLEAR optional |
Replaces any LF characters (line feed) that occur between text qualifiers with space characters. You must specify QUALIFIER with a char value to use LFCLEAR. If you use both CRCLEAR and LFCLEAR, CRCLEAR must come first. |
|||||||||||||||
REPLACENULL optional |
Replaces any NUL characters that occur in the delimited file with space characters. The number of any replaced NUL characters is recorded in the log. |
|||||||||||||||
ALLCHAR optional |
The Character data type is automatically assigned 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. ALLCHAR 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. |
|||||||||||||||
ALLFIELDS |
All fields in the source data file are imported. For information about how Analytics assigns data types when you use ALLFIELDS, see Remarks. Note If you specify ALLFIELDS, do not specify any individual FIELD syntax, or IGNORE. |
|||||||||||||||
FIELD name type |
The individual fields to import from the source data file, including the name and data type of the field. To exclude a field from being imported, do not specify it. For information about type, see Identifiers for field data types. Note type is ignored if you specify ALLCHAR. |
|||||||||||||||
AT start_position |
The starting byte position of the field in the Analytics data file. Note
In Unicode Analytics, typically you should specify an odd-numbered starting byte position. Specifying an even-numbered starting position can cause characters to display incorrectly. |
|||||||||||||||
DEC value |
The number of decimals for numeric fields. Note DEC is ignored if you specify ALLCHAR. |
|||||||||||||||
WID bytes |
The length in bytes of the field in the Analytics table layout. Note
In Unicode Analytics, specify an even number of bytes only. Specifying an odd number of bytes can cause characters to display incorrectly. |
|||||||||||||||
PIC format |
Note Applies to numeric or datetime fields only.
format must be enclosed in quotation marks. Note PIC is ignored if you specify ALLCHAR. |
|||||||||||||||
AS display_name |
The display name (alternate column title) for the field in the view in the new Analytics table. Specify display_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the column title. AS is required when you are defining FIELD. To make the display name the same as the field name, 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 <...n> optional |
Excludes the field from the table layout. field_num specifies the position of the excluded field in the source data file. For example, IGNORE 5 excludes the fifth field in the source data file from the Analytics table layout. Note The data in the field is still imported, but it is undefined, and does not appear in the new Analytics table. The data can be defined later, if necessary, and added to the table. To completely exclude a field from being imported, do not specify it when you specify fields individually. |
Examples
Import all fields
You import all fields from a comma delimited file to an Analytics table named Employees. The file uses double quotation marks as the text qualifiers. Data types are automatically assigned based on the set of rules outlined in Remarks:
IMPORT DELIMITED TO Employees "Employees.fil" FROM "Employees.csv" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE ALLFIELDS
Import all fields, automatically assign a Character data type
You import all fields from a comma delimited file to an Analytics table named Employees. The file uses double quotation marks as the text qualifiers. The Character data type is automatically assigned to all imported fields:
IMPORT DELIMITED TO Employees "Employees.fil" FROM "Employees.csv" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE ALLCHAR ALLFIELDS
Import specified fields, automatically assign a Character data type
You import specified fields from a tab delimited file to an Analytics table named Employees. The file uses double quotation marks as the text qualifiers. The Character data type is automatically assigned to all imported fields:
IMPORT DELIMITED TO Employees "Employees.fil" FROM "Employees.csv" 0 SEPARATOR TAB QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE ALLCHAR FIELD "First_Name" C AT 1 DEC 0 WID 25 PIC "" AS "First Name" FIELD "Last_Name" C AT 26 DEC 0 WID 25 PIC "" AS "Last Name" FIELD "CardNum" C AT 51 DEC 0 WID 16 PIC "" AS "Card Num" FIELD "EmpNo" C AT 67 DEC 0 WID 6 PIC "" AS "Emp Num" FIELD "HireDate" C AT 73 DEC 0 WID 10 PIC "" AS "Hire Date" FIELD "Salary" C AT 83 DEC 0 WID 5 PIC "" AS "" FIELD "Bonus_2016" C AT 88 DEC 0 WID 10 PIC "" AS "Bonus 2016"
Import specified fields, assign data types individually
You import specified fields from a semi-colon delimited file to an Analytics table named Employees. The file does not use text qualifiers. You specify the data type of each imported field:
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 25 PIC "" AS "First Name" FIELD "Last_Name" C AT 26 DEC 0 WID 25 PIC "" AS "Last Name" FIELD "CardNum" C AT 51 DEC 0 WID 16 PIC "" AS "Card Num" FIELD "EmpNo" C AT 67 DEC 0 WID 6 PIC "" AS "Emp Num" FIELD "HireDate" D AT 73 DEC 0 WID 10 PIC "MM/DD/YYYY" AS "Hire Date" FIELD "Salary" N AT 83 DEC 0 WID 5 PIC "" AS "" FIELD "Bonus_2016" N AT 88 DEC 2 WID 10 PIC "" AS "Bonus 2016"
Remarks
For more information about how this command works, see Import a delimited text file.
How Analytics assigns data types when you use ALLFIELDS
When you use the ALLFIELDS parameter, instead of defining fields individually, Analytics examines a subset of records at the beginning of the delimited file and assigns data types to fields based on the set of rules outlined below.
Once the data is in Analytics, if required you can assign different data types, such as Numeric or Datetime, to the fields, and specify format details.
Description of field values in the delimited file | Examples | Data type assigned |
---|---|---|
Values enclosed by text qualifiers | "ABC Suppliers" "6,990.75" |
Character |
Values include a non-numeric character anywhere in the field, with the exception of commas and periods used as numeric separators, and the negative sign (-) | $995 (995) |
Character |
Values include only numbers, numeric separators, or the negative sign | 6,990.75 -6,990.75 995 |
Numeric |
One or more blank values occur in a field | Character | |
Datetime values with separators, or alpha months |
2016/12/31 31 Dec 2016 |
Character |
Datetime values that are all numbers | 20161231 | Numeric |
Identifiers for field data types
The table below lists the letters that you must use when specifying type for FIELD. Each letter corresponds to an Analytics data type.
For example, if you are defining a Last Name field, which requires a character data type, you would specify "C": FIELD "Last_Name" C.
For more information, see Data types in Analytics.
Note
When you use the Data Definition Wizard to define a table that includes EBCDIC, Unicode, or ASCII fields, the fields are automatically assigned the letter "C" (for the CHARACTER type).
When you enter an IMPORT statement manually, or edit an existing IMPORT statement, you can substitute the more specific letters "E" or "U" for EBCDIC or Unicode fields.
Letter |
Analytics Data type |
---|---|
A |
ACL |
B |
BINARY |
C |
CHARACTER |
D |
DATETIME |
E |
EBCDIC |
F |
FLOAT |
G |
ACCPAC |
I |
IBMFLOAT |
K |
UNSIGNED |
L |
LOGICAL |
N |
|
P |
PACKED |
Q |
BASIC |
R |
MICRO |
S |
CUSTOM |
T |
PCASCII |
U |
UNICODE |
V |
VAXFLOAT |
X |
NUMERIC |
Y |
UNISYS |
Z |
ZONED |