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.

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 Excel file you want to create the ACL table from and click Open.

    Microsoft Excel files have an .xls or an .xlsx file extension.

  5. In the File Format page, verify that the Excel file option has been selected and click Next.
  6. In the Data Source page, complete the following steps:
    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 resulting file name or ACL table name.

    2. Leave Use first row as Field Names selected, or deselect it.
      • Leave the option selected if the Excel file has column headers that you want to use as field names in the ACL table. The headers must be in the first row in the Excel file.

      • Deselect the option if the Excel file does not have column headers, or if you do not want to use the column headers as field names in the ACL table.

    3. Under Detect type and length based on, leave First 100 records selected, or select Entire Excel Worksheet or Named Range.
      Important

      Be careful how you set this option. For information about the implications of the two settings, see Guidelines for defining Microsoft Excel files.

    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 after the date, you can shorten the length of the field to omit the empty time data.

      Tip

      If you used the First 100 records option in the previous step and you are uncertain whether the length of values in one or more fields is consistent throughout the Excel file, double the Length for any field you are uncertain about.

    • Type – Keep the data type assigned by ACL, or select an 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) 2015 ACL Services Ltd. All Rights Reserved.