Rules for Using Microsoft Excel Files with AgilePoint NX

This topic provides the rules to use Microsoft Excel files in AgilePoint NX.

You can use Microsoft Excel files for these purposes, and there are different rules for each:

Excel as a Data Source for Apps

You can use a Microsoft Excel file as a data source for an app. For example, you can use Excel to populate a lookup on an eForm or retrieve data for some process activities.

This section gives the rules for Microsoft Excel files when they are used as a data source for an app.

Examples

Table Name

  • For AgilePoint NX to read the data in Excel, it must be defined as a table.
  • The table name functions like a database table name. This name lets AgilePoint NX locate your data in the Excel file.

  • For the table name, the supported characters are:

    • English letters
    • Numbers
    • Underscores (_)

Column Names

  • AgilePoint NX uses the text in the first row as the column, or field, name.
  • Each column name must have a unique text string.

  • The supported characters in a column name are:

    • English letters
    • Numbers
    • Underscores (_)

Data and Data Types

  • The cells in each column must use the same data type.
  • All cells in a column must match the data type that is configured for the corresponding column header. If any cells do not match this data type, AgilePoint NX does not retrieve any data from the Excel file at runtime.

  • AgilePoint NX can detect these Excel data types:

    • Text
    • Date
    • Date Time
    • Decimal
    • Numeric
    • Percentage
    • Currency
  • Excel formulas are not supported.
  • These rules apply to records, or data rows:
    • The data in each row after the first row is imported as a record.
    • Empty rows will be ignored during import.
    • If the Excel file does not contain data in any rows after the first row, which contains the column names, the row is imported with no records. Records can be imported later if necessary.

Import Data from Excel to a Subform

You can use a Microsoft Excel file to import data in a repeating subform in an app at runtime.

This section gives the rules for Microsoft Excel files when they are used as a data source for an app.

Examples

Sheet Name

  • Only the first sheet in an Excel file will be imported.
  • If the Excel file contains more than one sheet, only the first sheet in the Excel file is imported.

Column Names

  • AgilePoint NX uses the text in the first row as the column, or field, name.
  • Each column name must have a unique text string.

  • The supported characters in a column name are:

    • English letters
    • Numbers
    • Spaces
    • Special characters
  • A column name cannot be an integer.
  • An Excel sheet that does not have labels in the first row (the first row is blank) cannot be imported.
  • If the first row of any column that has data is blank, the column is not imported.
  • If an Excel sheet has record data in the first row, this record data will be used as the column names.
  • During import, you can specify columns you do not want to include to import.

Data and Data Types

  • The cells in each column must use the same data type.
  • All values in a column must match the data type for the corresponding form control. If any values do not match this data type, the values are ignored during import.

  • AgilePoint NX can detect these Excel data types:

    • Text
    • Date
    • Date Time
    • Decimal
    • Numeric
    • Percentage
    • Currency
  • Excel formulas are not supported.

  • These rules apply to records, or data rows:

    • The data in each row after the first row is imported as a record.
    • Empty rows will be ignored during import.
  • The Excel file must be saved to your local machine to import it.

Downloadable Excel Template

  • The template file is a Microsoft Excel file where the column headers are the Internal Names for the form fields in the Subform form control.

  • If you use the Excel template downloaded from the subform at runtime, you can skip the mapping step when you import it to the subform under these conditions:

    • You do not change the values in the column headers. These values are the internal names for the fields in the subform.
    • You do not change the order of the columns.
    • You do not change the sheet name.
    • You do not change the data types for any cells in the spreadsheet.

Excel with Data Entities

You can use a Microsoft Excel file for these purposes in Data Entities:

  • Add data in an entity.
  • Change data in an entity.
  • Create a new entity with no data.
  • Create a new entity with data.

This section gives the rules for Microsoft Excel files when they are used as a data source for an app.

Sheet Name

  • Only the first sheet in an Excel file will be imported.
  • If the Excel file contains more than one sheet, only the first sheet in the Excel file is imported.
  • The name of the Excel sheet becomes the entity name.
  • You can change this name in AgilePoint NX Data Entities during import.

  • The sheet name must meet these requirements:

    • No more than 27 characters
    • No spaces or special characters
    • Must start with a letter

Column Names

  • AgilePoint NX uses the text in the first row as the column, or field, name.
  • Each column name must have a unique text string.

  • The supported characters in a column name are:

    • English letters
    • Numbers
  • A column name cannot have spaces or special characters. These characters are removed during import.
  • A column name must start with a letter.
  • A column name cannot be more than 30 characters. If a column name is more than 30 characters, it will be truncated during import.
  • All columns in the first row of the spreadsheet are considered column names. The column names become the entity field names.
  • An Excel sheet that does not have labels in the first row (the first row is blank) cannot be imported.
  • If the first row of any column that has data is blank, the column is not imported.
  • If an Excel sheet has record data in the first row, this record data will be used as the column names.
  • During import, you can specify columns you do not want to include to import.
  • A primary column is required so that each row has a unique identifier. This column represents the primary field for your entity.
    • The primary column must use the Text data type.
    • Each cell in the primary column must have a unique value.
    • Each cell in the primary column must have a value.
    • If your Excel file does not have a primary column, AgilePoint NX creates a primary field with the Auto Increment data type. This data type can be changed during import.

Data and Data Types

  • The cells in each column must use the same data type.
  • All cells in a column must match the data type for the corresponding entity field. Any cells that do not match this data type are ignored during import.

  • AgilePoint NX can detect these Excel data types:

    • Text
    • Date
    • Date Time
    • Decimal
    • Numeric
    • Percentage
    • Currency
  • Excel formulas are not supported.

  • During import, AgilePoint NX uses an algorithm to apply a data type if one is not specified in the Excel file. These rules apply to the data type algorithm. The data type suggested by AgilePoint NX can be changed during import:

    • The Currency data type is converted to Decimal.
    • If the cells in a column match more than one data type, the Text data type is applied.

  • These rules apply to records, or data rows:

    • The data in each row after the first row is imported as a record.
    • Empty rows will be ignored during import.
    • If the Excel file does not contain data in any rows after the first row, which contains the column names, the entity is imported with no records. Records can be imported later.
  • The Excel file must be saved to your local machine to import it.

Downloadable Excel Template - Create an Entity

  • You can use the Excel template to create records in a new entity, or you can create an entity with no records.
  • It is recommended to use an Excel template downloaded from an existing entity to create a new entity to ensure the data format is correct. You can change the sheet name (which is the entity name) and the name and order of the columns, but the template must follow the rules for Excel templates.

Downloadable Excel Template - Update Data in an Entity

  • You can use the Excel template to add new records to the entity or update existing records.
  • To import records into an existing entity, you must use the Excel template downloaded from the Data Entities component. If you imported the entity from an Excel file, do not use the file you imported. Use the downloaded template. If you are importing data to an entity that was not originally imported from an Excel file, do not attempt to create your own Excel template. Using the Excel template downloaded from AgilePoint NX ensures the data format is correct.
  • When you update the data in the Excel template, you must not change the first row of the template. This row contains the column names. These names must be the same in the downloaded Excel template and in the file you import.
  • If you use an Excel file to change some, but not all, of the data in an entity, download the Excel file populated with the data from the entity, change the necessary data, and then import the Excel file.
  • In an Excel template downloaded from AgilePoint NX, the correct data types are configured.
  • In an Excel template downloaded from AgilePoint NX, the correct sheet name is configured.