Guidelines for defining Microsoft Excel files

The sections below outline guidelines you need to follow when defining Microsoft Excel files and importing Excel data.

Maximum numbers of columns and characters

The maximum number of Excel columns, and the maximum number of characters in a field, that you can define and import from .xlsx or .xlsm files is not limited to a specific number. Instead, defining and 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.

When the newly defined table opens in ACL, a maximum of 256 columns are displayed in the default view. If the table contains additional columns, you can manually add them to the view, if required.

Determining data type and field length

If you leave First 100 records selected in the Data Source page of the Data Definition Wizard, ACL uses only the first 100 records in the Excel file to determine the data type of fields, and the length of fields, in the resulting ACL table. You should use the First 100 records option only if you are certain that all values in columns in the Excel file critical to subsequent analysis conform to the data types and field lengths established by the first 100 records.

Using the First 100 records option significantly speeds up the import process. However, if any values after the first 100 records are of a different data type, or are longer, than values in the first 100 records, the resulting ACL table will contain inaccurate or truncated data. Inaccurate or truncated data in critical fields will very likely invalidate the results of subsequent data analysis.

If you select Entire Excel Worksheet or Named Range in the Data Source page, ACL uses all the records in the Excel file or named range to determine the data type of fields, and the length of fields, in the resulting ACL table. You should use this option if you are uncertain about the consistency of the data types or the length of values in the Excel file.

With large Excel tables, using all the records to determine data type and field length significantly slows down the import process. If the time required to import Excel data using this option is an issue, one approach is to select First 100 records and then manually specify longer field lengths in the subsequent page in the Data Definition Wizard.

Data types and missing data

To get the best results when defining tables from Excel files ensure that each column in the Excel file contains the same type of data and that there are no blank rows or columns in the worksheet or named range.

(C) 2015 ACL Services Ltd. All Rights Reserved.