Home >

On this page

AX2432

Import source: Excel file

You can import data from an Excel file. The Excel file must meet the following criteria:

  • The file format must be XLS or XLSX. XLSM files cannot be imported.

  • The first row can optionally contain header names.

  • Each column in the Excel file translates to a column in the destination table. Each row in the file translates to a data record in the table. Blank columns and rows are ignored.

  • The data in the spreadsheet must match the designated data type for the destination column. For example, if numeric values in the spreadsheet are prefixed with a quotation mark, then Excel considers those values to be strings instead of numbers. This will cause an error if attempting to import these string values into a numeric column.

The Source tab of the Import Wizard uses the following settings when importing from an Excel file.

Item Description
Source Select Excel File to import data from an Excel file.

Remote Data Connection

If your Axiom Software system is hosted on the Axiom Cloud Service, and you are not using the "prompt for file" option (see the File Location setting), then you must specify a remote data connection so that the cloud service can read the file located on your network.

You can select any remote data connection defined in your system, or you can enter an import variable name. For example, you can enter the built-in system variable {DefaultRemoteDataConnection} to automatically use the default remote data connection for your system. For information on how the default remote data connection is determined, see System variables.

Remote data connections are defined in Scheduler. For more information, see Managing remote data connections.

If no remote data connections have been defined in your system, then this setting does not apply and will not display.

Sheet name

The sheet in the Excel file to import. Leave this blank to use the first sheet in the file. Only one sheet can be imported.

Variables can be used to specify the sheet name. See Using variables in imports.

File Location

The name and location of the source file. Select one of the following options:

  • Always use this file: Select this option if the file name and location is always known. In the File box, enter the path and file name. You can click the folder icon to navigate to the file.

    The file path must be a UNC path (i.e. \\servername\foldername\filename). If you enter a mapped drive, it will automatically be converted to a UNC path.

    The location of this file must be accessible to the Axiom Application Server. When you specify a file, Axiom Software will validate whether the application server can access the file, and will display an error if not. For assistance in resolving this error, see Troubleshooting file access.

  • Prompt for file during execution: Select this option if you want the user to be able to specify the file when running the import. If desired, in the Folder box, you can specify a folder location. When the user is prompted to select a file, it will open to this folder by default. The user can still browse to a different location.

    In this case, a copy of the specified file is uploaded to the application server for processing. Once the import is complete, the temporary copy of the file is deleted.

    NOTE: Files greater than 100 MB cannot be uploaded using the "prompt" option. While it is possible to increase this limit, it is not recommended. Instead, you should use Always use this file if you need to import a file larger than 100 MB. Please contact Kaufman Hall Software Support if you need assistance with a large file.

Variables can be used to specify the file name or location. See Using variables in imports.

First row has column names

Select this option if the first row of the file contains column names. If the first row of the file contains data, leave this option unchecked.