Data validation for tab-delimited files

When you import a tab-delimited file, validation criteria depends on the type of record and the type of data contained in the column.

These values are checked during the first phase of validation:

  1. Column heading row

    The column headings row must be the first row in the tab-delimited file. The row must contain these columns:

    • extractseqno
    • rowpointer
    • original key columns

    If the requirements for the column heading row are not met, import processing stops. You must fix the column heading row in Excel, and then import the file again.

  2. Key column values

    Each column within each row is validated. If double quotes (") were placed around the content of a cell in Excel, the quotes are removed before validation occurs.

    Validations are performed on these data types:
    • Integers: The field cannot be blank. The data must be numeric, but it cannot contain a decimal point and decimal digits.
    • Decimals: The field cannot be blank. The data must be numeric, and it can contain a decimal and decimal digits.
    • Character, or string: This field can be blank. If a value is specified, the length of the data is checked. If it exceeds the length that is established by the database schema and format, the warning is included on the SA Mass Maintenance Utility Report. This type of warning is not a validation error. It does not stop the import process.
    • Logical, or boolean: The field cannot be blank. The data must be one of these values: yes, no, true, or false.
    • Date: The field can be blank. If a value is specified, the data must be a valid Progress date, such as mm/dd/yy.

    This table shows the key values that are exceptions to the rule. These fields can be blank.

    Table Key value
    OESSRE-Order Entry Setup Sales Rep Exceptions

    custno

    shipto

    prodcatending
    ICSL-Product Line Master whse
  3. Non-key column values

    Each column within each row is validated. If double quotes (") were placed around the content of a cell in Excel, the quotes are removed before validation occurs.

    1. Validations are performed on these data types:
      • Integers: The field cannot be blank. The data must be numeric, but it cannot contain a decimal point and decimal digits.
      • Decimals: The field cannot be blank. The data must be numeric, and it can contain a decimal and decimal digits.
      • Character, or string: This field can be blank. If a value is specified, the length of the data is checked. If it exceeds the length that is established by the database schema and format, the warning is included on the SA Mass Maintenance Utility Report. This type of warning is not a validation error. It does not stop the import process.
      • Logical, or boolean: The field cannot be blank. The data must be one of these values: yes, no, true, or false.
      • Date: The field can be blank. If a value is specified, the data must be a valid Progress date, such as mm/dd/yy.
    2. Validations are performed on common entities. This table shows the records that are used to validated common entities and the data type.
      Field name Data Type Record
      arpvendno on the ICSW table Decimal APSV, if the value is not 0.
      creditmgr Character SASOO
      nontaxtype Character

      SASTA for Code ID = N

      This value is maintained in SA Table Code Value Setup.
      pricetype Character

      SASTA for Code ID = J for ARSC and APSC, or Code ID = K for ICSW and ICSC

      This value is maintained in SA Table Code Value Setup.
      prodcat Character

      SASTA for Code ID = C

      This value is maintained in SA Table Code Value Setup.
      shipviaty Character

      SASTA for Code ID = S

      This value is maintained in SA Table Code Value Setup.
      slsrepin Character

      SMSN

      This value is maintained in Sales Rep Setup.
      slsrepout Character

      SMSN

      This value is maintained in Sales Rep Setup.
      speccostty Character

      Value must be blank, Y, T, or H.

      This value in maintained in Product Setup.
      termstype Character

      SASTA for Code ID = T

      This value is maintained in SA Table Code Value Setup.
      vendno for ICSC table Decimal APSV, if the value is not 0.
      whse, not in the ICSD table Character ICSD
  4. Record type
    1. The values in the extractseqno and rowpointer columns are checked. These two conditions are valid:
      • The value for extractseqno is not zero and the rowpointer is not blank. This condition indicates that the record is an existing record.
      • The value for extractseqno is zero and the rowpointer is blank. This condition indicates that the record is a new record.
    2. These validations are performed for existing and new records:
      • Existing records that require updates are compared to the corresponding extraction records in the temporary database table for the set. A validation error occurs if one of these conditions are met:
        • One of these values from the tab-delimited file does not match the corresponding value for the extraction record in the temporary database table.
          • extractseqno
          • rowpointer
          • Key values
        • The Status Type of the record is Completed.
        • The record is flagged for deletion from the database.
        • The record was previously identified as a new record.

      • For new records, this validation is performed:

        • Function security for the menu function for the database table. Level 4 is required to create a new record.
        • Common entities validation as previously described.