IMPORT MULTIDELIMITED command
Concept Information
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. ExampleTO "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:
Note Any delimited file names that exceed 64 characters are truncated when imported to Analytics. Analytics table names are limited to 64 alphanumeric characters, not including the .FIL extension. As well, any of the following characters in a delimited file name are replaced with the underscore character ( _ ) in the Analytics table name:
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. ExampleFROM "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. ExampleFROM "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. ExampleFROM "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. ExampleFROM "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:
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:
|
|||||||||||||||
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 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:
|
Text qualifier character | QUALIFIER | Source delimited files use a different text qualifier character to wrap and identify field values. |
Do one of the following:
|
Start line of the data | STARTLINE |
Source delimited files have different start lines for the data. |
Do one of the following:
|
Field names | KEEPTITLE | Source delimited files have field names on different line numbers. |
Do one of the following:
|
Field names | KEEPTITLE | Some source delimited files have field names and some do not. |
Do one of the following:
|
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.