IMPORT EXCEL command

Used to define ACL tables from Microsoft Excel worksheets or named ranges.

Syntax

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

 

field_syntax ::=
FIELD name type {PIC value|WID value DEC value} AS value

Parameters

TO table

Specifies the name of the ACL table to import the data into.

import_filename

Specifies the name of the ACL data file. The import_filename parameter must be specified as a quoted string with a .FIL file extension (e.g., "Invoices.FIL").

If you do not want to create the .FIL file in the same directory as the ACL project, you can specify an absolute or relative file path to an existing folder to write the .FIL file to.

For example: "C:\data\Invoices.FIL" or "data\Invoices.FIL"

FROM source_filename

Specifies the name of the source data file. This parameter must be specified as a quoted string.

If the source data file is not located in the same directory as the ACL project, you must use an absolute path (e.g., "C:\data\source_filename") or a relative path (e.g., "data\source_filename") to specify the file location.

TABLE worksheet_or_named_range

Specifies the Microsoft Excel worksheet or the named range in the source data file to use. If you are importing data from a worksheet, you must enter a “$” sign at the end of the worksheet name. The worksheet_or_named_range parameter must be specified as a quoted string.

CHARMAX max_field_length

If you are not defining fields individually, specifies the maximum length for any field in the ACL table that originates as character data in the source data file. The CHARMAX parameter is required if you are not defining fields individually, even if none of the source data is character.

KEEPTITLE

Optional. Specifies that the first row of Excel data is used as field names in the ACL table rather than as the first record. If this keyword is omitted, the ACL table uses generic field names and the first row of data becomes the first record.

If you are defining fields individually, the KEEPTITLE parameter must appear before the first FIELD parameter. If you are not defining fields individually, the KEEPTITLE parameter must appear after the CHARMAX parameter.

FIELD name type

Specifies the name and data type of the field. For information on the values you can enter for the type parameter, see Identifiers for field data types.

PIC value

Specifies the format for date and numeric fields. For numeric fields the format specifies how the values should be displayed. For date fields, the format specifies how dates are stored and should be read from the data source.

WID value

Specifies the length of the field in the ACL table layout.

DEC value

Specifies the number of decimals.

AS value

Specifies the display label (alternate column title) for the field in the ACL view. The AS parameter is required when you are defining a field. If you want the display label to be the same as the field name, you can enter a blank value using the following syntax: AS "". Make sure there is no space between the two double quotation marks.

IGNORE field_num

Optional. Specifies that the field should not be included in the table layout. The field_num parameter specifies the position of the field in the source data. For example, IGNORE 5 excludes the fifth field in the source data from the ACL table layout.

OPEN

Optional. Specifies that the table created by the command should be opened after the command executes. This parameter is only valid if the command creates an output table.

Remarks

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

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. Instead, importing from these Excel file types is governed by the record length limit in ACL data files (.fil) of 32 KB. If any record in the source Excel file would create an ACL record longer than 32 KB, the import fails.

The import of .xls files uses a different type of processing, and is subject to maximums of 255 columns, 255 characters per field, 32 KB per record, and 65,000 rows.

Examples

The first example defines a new ACL table called “Credit_Cards” that uses the first row of Excel data as the field names. The ACL table defines and includes three fields from the source table, but excludes the remaining 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

The second example defines a new ACL table called “May_Transactions” that uses the first row of Excel data as the field names. The ACL table includes all fields from the source table and uses default field definitions. 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, whichever is shorter.

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


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback