Changing or adding data in Excel

To perform extensive changes to the extracted records, or to create new records, you can use the export and import function.

To use this method, you must perform these steps:

  1. Export the extractions records to a tab-delimited file.
  2. Edit the file in Excel to change existing records and create new records.
  3. Import the file to update the set.

The data that you import into SA Mass Maintenance Administration only updates the extraction records. You must perform a final update to update the database.

You can create the tab-delimited file when you create the set. If the final update was not performed on a set, you can also create a tab-delimited file after a set is created. To include the most current values from the extraction records, create the tab-delimited file immediately before you edit the extraction records in Excel.

To avoid overriding changes that were previously made to extraction records, export the extraction records immediately before you edit the records in Excel. The latest updates to the extraction records are then included in the tab-delimited file.

The tab-delimited file contains a header row. Each row after the header row represents an extraction record. The tab-delimited file includes these columns and columns types:

  • extractseqno

    The sequence number for the extraction record.

  • Key columns

    Keys that are associated with extraction record. The file can contain multiple key columns.

  • source-desc-name

    The name or description that identifies the original record.

  • Extraction columns

    The database columns that are included in the set.

  • rowpointer

    The rowpointer for the original record.

You can create new records in a set that was created specifically to create new records, or in a set in which existing records were edited. In either type of set, ensure that you extract the fields that are required for a new record to avoid errors during final update. If you create a set specifically to create new records, extract at least one record from the database table. Use that record as an example of data types and formats for the new records.

Note: If you create a set specifically to create new records, save the set. You can then copy the set when you create subsequent sets to create additional records.

When you import the tab-delimited file, it is copied to the Print Directory that is specified in SA Company Setup. The file is then validated and the extraction records are updated in SA Mass Maintenance Administration.

Validation occurs in two phases. In the first phase, the format and contents of the tab-delimited file is validated.

See Data validation for tab-delimited files.

If errors occur during the first phase, they are included on the SA Mass Maintenance Utility Report. You must correct the errors before you can continue the import process.

In the second phase, the extraction records that were updated or created in Excel are processed. Field values from the original records are compared to the values in the tab-delimited file. If a value on a record is different, these Status values are assigned to the extraction record:

  • Status Operation: Update
  • Status Type: Pending

If a row is identified as a new record, it is added to the Extraction Records grid and assigned the next available sequence number. These Status values are assigned the new records:

  • Status Operation: Create
  • Status Type: Pending