IMPORT EXCEL command

Creates an Analytics table by defining and importing a Microsoft Excel worksheet or named range.

Syntax

IMPORT EXCEL TO table import_filename FROM source_filename TABLE input_worksheet_or_named_range <KEEPTITLE> {ALLFIELDS|CHARMAX max_field_length|[field_syntax] <...n> <IGNORE field_num> <...n>} <OPEN>

field_syntax ::=
FIELD import_name type {PIC format|WID characters DEC value} 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"
TABLE worksheet_or_named_range	The Microsoft Excel worksheet or the named range in the source data file to import: you must enter a "$" sign at the end of a worksheet name worksheet_or_named_range must be specified as a quoted string
KEEPTITLE optional	Treat the first row of data as field names instead of data. If omitted, generic field names are used. If you are defining fields individually, KEEPTITLE must appear before the first FIELD.
ALLFIELDS	All fields in the source data file are imported. Note If you specify ALLFIELDS, do not specify any individual FIELD syntax, CHARMAX, or IGNORE.
CHARMAX max_field_length	Only applies when you are not defining fields individually. The maximum length in characters for any field in the Analytics table that originates as character data in the source data file.
FIELD import_name type	The individual fields to import from the source data file, including the name and data type of the field. import_name becomes the field name in the Analytics table. import_name does not need to be the same as the field name in the source data file, although it can be. Tip You can additionally use AS to specify a display name that is different from import_name. type becomes the field date type in the Analytics table. type does not need to be the same as the field data type in the source data file, although it can be. For more information about type, see Identifiers for field data types. Excluding a field To exclude a field from being imported, do not specify it. You must also specify IGNORE for excluded fields.
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.
WID characters	The length in characters of the field in the Analytics table layout.
DEC value	The number of decimals for numeric fields.
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 Be careful to correctly align field_num with the position of excluded fields. If you specify field_num for an included field (FIELD definition), or for a field position that does not exist, the import does not work correctly. The number of FIELD and IGNORE parameters combined must equal the total number of fields in the source data table. If the total numbers do not match, the import does not work correctly. If you specify ALLFIELDS, do not specify IGNORE.
OPEN optional	Opens the table created by the command after the command executes. Only valid if the command creates an output table.

Examples

Importing specified fields

You perform an import that defines a new Analytics table called Credit_Cards. It uses the first row of Excel data as the field names.

From the twelve fields in the source table, the Analytics table defines and includes three fields and excludes nine fields:

IMPORT EXCEL TO Credit_Cards "Credit Cards.fil" FROM "Credit_Cards_Metaphor.xls" TABLE "Corp_Credit_Cards$" KEEPTITLE FIELD "CARDNUM" N WID 16 DEC 0 AS "Card Number" FIELD "EXPDT" D WID 10 PIC "YYYY-MM-DD"  AS "Expiry Date" FIELD "PASTDUEAMT" N WID 6 DEC 2 AS "Past Due" IGNORE 2 IGNORE 3 IGNORE 5 IGNORE 6 IGNORE 7 IGNORE 9 IGNORE 10 IGNORE 11 IGNORE 12

Importing all fields

You perform an import that defines a new Analytics table called May_Transactions. It uses the first row of Excel data as the field names.

The Analytics table includes all fields from the source table and uses default field definitions.

Field length set to longest value

In the first example, fields that originate as character data in the source data file are set to the length of the longest value in the field:

IMPORT EXCEL TO May_Transactions "May_Transactions.fil" FROM "Trans_May.xls" TABLE "Trans1_May$" KEEPTITLE ALLFIELDS

Field length constrained

In the second example, fields that originate as character data in the source data file are set to the length of the longest value in the field, or to the CHARMAX value of 100 characters, whichever is shorter:

IMPORT EXCEL TO May_Transactions "May_Transactions.fil" FROM "Trans_May.xls" TABLE "Trans1_May$" KEEPTITLE CHARMAX 100

Remarks

Note

For more information about how this command works, see the Analytics Help.

Define fields individually, or import all fields using a default definition

When you import an Excel file to an Analytics table you can use FIELD parameters to define each field individually, or you can use the ALLFIELDS parameter, or the CHARMAX parameter, to import all fields using default Analytics field definitions.

Maximum size of data import

File format .xlsx or .xlsm

The maximum number of Excel columns, and the maximum number of characters in a field, that you can import from .xlsx or .xlsm files is not limited to a specific number.

Importing from these Excel file types is governed by the record length limit in Analytics data files (.fil) of 32 KB. If any record in the source Excel file would create an Analytics record longer than 32 KB, the import fails.

File format .xls

The import of .xls (Excel 97 - 2003) files uses a different type of processing, and is subject to maximums of:

255 columns
255 characters per field
32 KB per record
65,000 rows

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 Analytics data types.