IMPORT EXCEL command

Concept Information

Import Microsoft Excel data

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> <STARTLINE line_number> <ALLCHAR> {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

Note

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

Analytics cannot import from an Excel workbook if Protected View is active for the workbook. You must first enable editing in the workbook, save and close the workbook, and then perform the import.

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 worksheet or the named range to import from the Microsoft Excel source data file.

Requirements:

  • add a "$" sign at the end of a worksheet name

    For example, TABLE "Corp_Credit_Cards$"

  • specify a named range exactly as it appears in Excel

    For example, TABLE "Employees_Sales"

  • specify worksheet_or_named_range as a quoted string
KEEPTITLE

optional
  • KEEPTITLE used with ALLFIELDS or CHARMAX 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.
STARTLINE line_number

optional

The line number on which to start reading the worksheet.

For example, if the first three lines of a worksheet contain header information that you do not want, specify STARTLINE 4 to start reading data on the fourth line.

If you omit STARTLINE, the start line is the first line in the worksheet.

Note

The start line for a named range is always the first line in the named range, regardless of the STARTLINE setting.
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 Excel 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.

Note

If you specify ALLFIELDS, do not specify any individual FIELD syntax, CHARMAX, or IGNORE.
CHARMAX max_field_length

The maximum length in characters for any field in the Analytics table that originates as character data in the source data file.

Source character data that exceeds the maximum is truncated.

All fields in the source data file, regardless of data type, are imported.

Note

If you specify CHARMAX, do not specify any individual FIELD syntax, ALLFIELDS, or IGNORE.
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.

Note

If you specify ALLCHAR, type is ignored.

If you specify individual FIELD syntax, do not specify ALLFIELDS or CHARMAX.

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 or CHARMAX, 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

Import 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" C WID 16 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

Import 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 50 characters, whichever is shorter:

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

Import all fields as character data

You perform an import that defines a new Analytics table called May_Transactions. All fields, including numbers and dates, are imported as character data.

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

Import all fields as character data, skip header information

You perform an import that defines a new Analytics table called Past_Due_Report.

You skip the first two rows of the Excel file, which contain report header information, and start reading the file on the third row, which contains field names. All fields, including numbers and dates, are imported as character data.

IMPORT EXCEL TO Past_Due_Report "Past_Due_Report.fil" FROM "Past_Due_Report.xlsx" TABLE "Sheet1$" KEEPTITLE STARTLINE 3 ALLCHAR ALLFIELDS

Remarks

For more information about how this command works, see Import Microsoft Excel data.

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.

Different combinations of parameters produce different results. The table below summarizes the different possibilities.

Note

"Define" means manually specifying things like field name, data type, length, datetime format, and so on.

I want to: Use these parameters: Do not use these parameters:
  • import all fields automatically with default definitions
  • if required, define fields post-import in Analytics
 ALLFIELDS CHARMAX, FIELD
  • import all fields automatically with default definitions
  • if required, define fields post-import in Analytics
  • truncate long character fields
CHARMAX ALLFIELDS, FIELD
  • define fields pre-import

FIELD

 ALLFIELDS, CHARMAX
  • define fields pre-import
  • exclude some fields from being imported

FIELD

IGNORE

 ALLFIELDS, CHARMAX
  • partially define fields pre-import
  • automatically import all fields as character data

ALLCHAR

FIELD

 ALLFIELDS, CHARMAX
  • omit blank rows or header information at the top of a worksheet
 STARTLINE  
  • use the first row of the worksheet as field names
 KEEPTITLE  
  • use the row of the worksheet specified by STARTLINE as field names

KEEPTITLE

STARTLINE

  

How Analytics assigns data types when you use ALLFIELDS or CHARMAX

When you use the ALLFIELDS or CHARMAX parameters, instead of defining fields individually, Analytics examines a subset of records at the beginning of the Excel file and assigns data types to fields based on a set of internal rules.

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.

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 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