IMPORT DELIMITED command

Concept Information

Import a delimited text file

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:

  • "C:\data\Invoices.FIL"
  • "data\Invoices.FIL"

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:

  • "C:\data\source_filename"
  • "data\source_filename"
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:

Code Analytics edition Source data encoding
0

Non-Unicode edition

 all data
0

Unicode edition

 ASCII data
2 Unicode edition Unicode data, UTF-16 LE encoding
3 numeric_code Unicode edition

Unicode data that does not use UTF-16 LE encoding

To determine the numeric code that matches the source data encoding, perform an import using the Data Definition Wizard, select the Encoded Text option, and find the matching encoding in the accompanying drop-down list.

To specify the code, specify 3, followed by a space, and then the numeric 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:

  • SEPARATOR "   " or SEPARATOR TAB
  • SEPARATOR " " or SEPARATOR SPACE
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:

  • QUALIFIER ""
  • QUALIFIER NONE

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
  • KEEPTITLE used with ALLFIELDS Treat the line number specified by STARTLINE as field names instead of data.

    If you omit KEEPTITLE, generic field names are used and the line number specified by STARTLINE is treated as data.

  • KEEPTITLE used with individual FIELD syntax Do not import the line number specified by STARTLINE. FIELD name specifies the field names.

    If you omit KEEPTITLE, the line number specified by STARTLINE is treated as data. FIELD name specifies the field names.
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

non-Unicode Analytics 1 byte = 1 character
Unicode Analytics 2 bytes = 1 character

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

non-Unicode Analytics 1 byte = 1 character
Unicode Analytics 2 bytes = 1 character

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.

  • numeric fields the display format of numeric values in Analytics views and reports
  • datetime fields the physical format of datetime values in the source data (order of date and time characters, separators, and so on)

    Note

    For datetime fields, format must exactly match the physical format in the source data. For example, if the source data is 12/31/2014, you must enter the format as "MM/DD/YYYY".

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

PRINT

P

PACKED

Q

BASIC

R

MICRO

S

CUSTOM

T

PCASCII

U

UNICODE

V

VAXFLOAT

X

NUMERIC

Y

UNISYS

Z

ZONED