What Are the Rules to Import Entities or Records from Microsoft Excel to AgilePoint NX Data Entities?

This topic gives the rules to import entities and records records from Microsoft Excel into AgilePoint NX Data Entities.

You can import data into AgilePoint NX Data Entities from a Microsoft Excel file. Because Excel is an open data entry format, you must format the Excel file so that it provides a data structure that AgilePoint NX can read and create an entity.

The formatting rules are different, depending upon whether you are creating a new entity, or you are importing data into an existing entity.

Create a New Entity

These are the rules to import an Excel file to create a new entity, with or without records.

  • Only the first sheet in an Excel file will be imported.

    If the Excel file contains more than one sheet, the first sheet in the Excel file will be imported, and the others will be ignored. If you want to import more than one sheet, create a separate Excel file for each sheet.

  • 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
  • All columns in the first row of the spreadsheet are considered column names. The column names become the entity field names.
    • Column names are required for all columns in the spreadsheet.
    • A blank spreadsheet that does not include column names can not be imported.
    • An Excel sheet that does not have labels in the first row (the first row is blank) can not be imported.
    • If an Excel sheet has record data in the first row, this record data will be used as the column names.
    • If a the first row of a column is blank, the column will not be imported.
    • Each column name must be unique.
    • A column name can not be more than 30 characters. If a column name is more than 30 characters, it will be truncated during import.
    • A column name can not have spaces or special characters. These characters are removed during import.
    • A column name must start with a letter.
  • The cells in each column must use the same data type.

    These rules apply to data types:

    • AgilePoint NX can detect these Excel data types:
      • Text
      • Date
      • Date Time
      • Decimal
      • Numeric
      • Percentage
      • Currency
    • 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.
  • During import, you can specify columns you do not want to include in Data Entities, and these columns will not be imported.
  • 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.
  • These rules apply to records, or data rows:
    • The data in each row after the first row is imported into Data Entities as a record.
    • Empty rows will be ignored during import.
    • If the Excel table 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 if necessary.
    • You can not import Excel formulas.

Import Records from an Excel File to an Existing Entity

If you want to import data from an Excel file to an existing entity, you can download a Microsoft Excel template for the entity.

If you import a new entity from Excel, and then you want to import records later, follow this procedure:

  1. Create an Excel file using the rules in the Create a New Entity section.
  2. Import the Excel file in Data Entities.
  3. Download an Excel template file from Data Entities that is generated from the entity you created.
    • 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.
    • During this procedure, some changes may occur in the Excel file format. For exmaple, the column names may change because the names are shortened or special characters are removed, and a primary column may be added if one did not exist in the original file. To minimize these changes, follow the rules in the Create a New Entity section when you create the Excel file to import.

  4. Add the record data in the Excel table rows in the downloaded Excel template file.
    • You can use the Excel template to add new records to the entity or update existing records.
    • 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.
  5. Import the Excel file you updated from the Excel template file.

    The imported records are added or changed in your entity based on the data in the Excel table rows.