Defining Microsoft Excel files

You can define an ACL table from Microsoft Excel files saved in the file format of any version of Excel between Microsoft Excel 3.0 and Microsoft Excel 2013. You can also define ACL tables from earlier versions than Excel 3.0, but you must use ODBC to access the data file or save the data in another file format that ACL can define. You can define Microsoft Excel data on your computer even if you do not have Microsoft Excel installed.

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.

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.

To create a table from a Microsoft Excel file:

  1. Select File > New > Table.
  2. If the Select Platform for Data Source page is displayed, select Local and click Next.
  3. In the Select Local Data Source page, select Disk and click Next.
  4. In the Select File to Define dialog box, locate and select the file you want to create the ACL table from and click Open. Microsoft Excel files have a .xls or .xlsx file extension.
  5. In the File Format page, verify that the Excel file option has been selected and click Next.
  6. Complete the following steps in the Data Source page:
    1. Select the worksheet or named range to import. Worksheets are identified with a dollar sign ($) appended to the worksheet name. The dollar sign is added temporarily, and does not appear in the file name or ACL table name.
    2. The Use first row as Field Names option is selected by default. If you want to use header information in the Excel file as the ACL field names, the header information must be in the first row in the file. If the data file does not include field names in the first row of the worksheet or named range, deselect this checkbox.
    3. Select the appropriate option to use for detecting the field types in the file. Select First 100 records if you want ACL to use the first 100 records to determine the column types. Typically, this subset of records is sufficient to determine the type of each column, and avoids performance issues associated with analyzing all records in large files. Select Entire Excel Worksheet or Named Range if ACL previously failed to determine the correct column types using the first 100 records, of if you want ACL to analyze all of the records in the file or named range to determine the column types.
    4. Click Next.
  7. In the Excel Import page, you can modify the name and properties for each field by selecting the appropriate column heading in the preview table, in the bottom half of the page, and updating any of the following properties:
    • Ignore this field – If you do not want the field to appear in the ACL table, select this checkbox.

    • Name – Keep the name assigned by ACL for the field in the table layout, or enter a different name.

    • Column Title – Enter the column title to display in the default ACL view. If a column title is not specified the Name value is used.

    • Length – Keep the value assigned by ACL for the field length in the table layout, or enter a different length.

      If a datetime field has no time data and is displaying 00:00:00, you can shorten the length of the field to omit the empty time data.

    • Type – Select the appropriate data type from the drop-down list. For information about the supported data types in ACL, see ACL data types.

      The Decimal and Input Format text boxes appear automatically when you select the corresponding data type.

    • Value – A read-only property that displays the first value in the field. The value is updated based on any edits you make.

    • Decimal (numeric fields only) – Specify the appropriate number of decimal places.

    • Input Format (datetime fields only) – Select a format that matches the data, or if necessary create a format to match the data. For more information about date and time formats, see Formats of date and time source data.

  8. Click Next after you have finished editing the field properties you want to change.
  9. In the Save Data File As dialog box, enter a name for the ACL data file, and if necessary modify the location where the file will be saved, and click Save.
  10. In the Final page, verify the settings for the new ACL table and click Finish.
  11. Enter a name for the ACL table you are adding to the project, or keep the default name, and click OK.

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