IMPORT MULTIDELIMITED command

Concept Information

Import a delimited text file

Creates multiple Analytics tables by defining and importing multiple delimited files.

Syntax

IMPORT MULTIDELIMITED <TO import_folder> FROM {source_filename|source_folder} source_char_encoding SEPARATOR {char|TAB|SPACE} QUALIFIER {char|NONE} <CONSECUTIVE> STARTLINE line_number <KEEPTITLE> <CRCLEAR> <LFCLEAR> <REPLACENULL> <ALLCHAR>

Note

You must specify the IMPORT MULTIDELIMITED parameters in exactly the same order as above, and in the table below.

To import multiple delimited files cleanly, the structure of all the files must be consistent before importing.

For more information, see Consistent file structure required.

Parameters

Name Description
TO import_folder

optional

The folder to import the data into.

To specify the folder, use an absolute file path, or a file path relative to the folder containing the Analytics project. Specify import_folder as a quoted string.

Example

TO "C:\Point of sale audit\Data\Transaction working data"
TO "Data\Transaction working data"

If you omit TO , the data is imported to the folder containing the Analytics project.

FROM source_filename | source_folder

The name of the source data files, or the folder containing the source data files.

Specify source_filename or source_folder as a quoted string.

The command supports importing four types of delimited file:

  • *.csv
  • *.dat
  • *.del
  • *.txt

Source data files in the root Analytics project folder

To specify multiple files, use a wildcard character (*) in place of unique characters in file names. The wildcard character stands for zero (0) or more occurrences of any letter, number, or special character.

Example

FROM "Transactions_FY*.csv"

selects:

Transactions_FY18.csv

Transactions_FY17.csv

You can use a wildcard in more than one location in a file name, and in a file extension.

Example

FROM "Transactions_FY*.*"

selects:

Transactions_FY18.txt

Transactions_FY17.csv

Source data files not in the root Analytics project folder

If the source data files are not located in the same folder as the Analytics project, you must use an absolute file path, or a file path relative to the folder containing the project, to specify the location of the files.

Example

FROM "C:\Point of sale audit\Data\Transaction master files\Transactions_FY*.csv"
FROM "Data\Transaction master files\Transactions_FY*.csv"

Folder containing source data files

Instead of specifying file names, you can just specify the name of the folder containing source data files. All supported delimited files in the folder are imported (*.csv, *.dat, *.del, *.txt).

To specify a source data folder, use an absolute file path, or a file path relative to the folder containing the Analytics project.

Example

FROM "C:\Point of sale audit\Data\Transaction master files"
FROM "Data\Transaction master files"
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.

Note

If you do not specify a code, Non-Unicode Analytics automatically uses 0 , and Unicode Analytics automatically uses 2 .
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 the data begins on.

For example, if the first four lines of data contain header information that you do not want, specify 5 for line_number.

Note

Ideally, the start line of the data should be the same in all the delimited files that you import with a single execution of IMPORT MULTIDELIMITED.

If start lines are different, see Consistent file structure required.
KEEPTITLE

optional

Treat the line number specified by STARTLINE as field names instead of data. If you omit KEEPTITLE, generic field names are used.

Note

The field names must be on the same line number in all the delimited files that you import with a single execution of IMPORT MULTIDELIMITED.

If field names are on different line numbers, see Consistent file structure required.
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.

Examples

The examples below assume that you have monthly transaction data stored in 12 delimited files:

  • Transactions_Jan.csv to Transactions_Dec.csv

Note

A separate Analytics table is created for each delimited file that you import.

Import all the delimited files

You want to import all 12 delimited files. You use the wildcard symbol (*) where the month occurs in each file name.

Analytics attempts to assign the appropriate data type to each field.

IMPORT MULTIDELIMITED FROM "Transactions_*.csv" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE

Import all the delimited files as character data

This example is the same as the one above, except Analytics automatically assigns the Character data type to all the imported fields.

IMPORT MULTIDELIMITED FROM "Transactions_*.csv" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE ALLCHAR

Import all the delimited files from the specified folder

You want to import all the delimited files in the C:\Point of sale audit\Data\Transaction master files folder.

IMPORT MULTIDELIMITED FROM "C:\Point of sale audit\Data\Transaction master files" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE

Import all the delimited files from the specified folder, and save the Analytics tables to another folder

This example is the same as the one above, but instead of saving the Analytics tables in the root project folder, you want to save them in the C:\Point of sale audit\Data\Transaction working data folder.

IMPORT MULTIDELIMITED TO "C:\Point of sale audit\Data\Transaction working data" FROM "C:\Point of sale audit\Data\Transaction master files" 0 SEPARATOR "," QUALIFIER '"' CONSECUTIVE STARTLINE 1 KEEPTITLE

Remarks

Consistent file structure required

To import a group of delimited files cleanly using IMPORT MULTIDELIMITED, the structure of all the files in the group must be consistent.

You can import inconsistently structured delimited files, and subsequently perform data cleansing and standardizing in Analytics. However, this approach can be labor intensive. In many cases, it is easier to make the delimited files consistent before importing.

To import multiple delimited files cleanly, the following items need to be consistent across all files:

Item ACLScript keyword Problem Solution

The character set and encoding of the source data

 numeric code

(Unicode edition of Analytics only)

Source delimited files use different character encodings. For example, some files have ASCII encoding and some files have Unicode encoding.

 Group source files by encoding type, and do a separate import for each group.
Delimiter character SEPARATOR Source delimited files use a different separator character (delimiter) between fields.

Do one of the following:

  • Standardize the separator character in the source files before importing them.
  • Group source files by separator character, and do a separate import for each group.
Text qualifier character QUALIFIER Source delimited files use a different text qualifier character to wrap and identify field values.

Do one of the following:

  • Standardize the qualifier character in the source files before importing them.
  • Group source files by qualifier character, and do a separate import for each group.
Start line of the data STARTLINE

Source delimited files have different start lines for the data.

Do one of the following:

  • Standardize the start line in the source files before importing them.
  • Group source files that have the same start line, and do a separate import for each group.
  • Make line_number equal to the lowest start line across all the files. Once the files have been imported to Analytics tables, you can use the EXTRACT command to extract only the records from any table with unwanted header information.
Field names KEEPTITLE Source delimited files have field names on different line numbers.

Do one of the following:

  • Standardize the line number with the field names in the source files before importing them.
  • Group source files that have field names on the same line number, and do a separate import for each group.
Field names KEEPTITLE Some source delimited files have field names and some do not.

Do one of the following:

  • Add field names to the source files that require them before importing all files.
  • Group source files that have field names, and files that do not have field names, and do a separate import for each group.
  • Omit KEEPTITLE to import all files using generic field names. Once the files have been imported to Analytics tables, you can use the EXTRACT command to extract only the data you want from any table.

Multiple IMPORT DELIMITED commands

The IMPORT MULTIDELIMITED command actually performs multiple individual IMPORT DELIMITED commands – one for each file imported. If you double-click the IMPORT MULTIDELIMITED entry in the log, the individual IMPORT DELIMITED commands are displayed in the display area.

Combining multiple delimited files after importing them

After you import multiple delimited 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.

For information about combining multiple Analytics tables, see APPEND command.