You are using an unsupported browser. Please update your browser to the latest version on or before July 31, 2020.
close
Home > Access It! > Access It! Features > Data Exchange Packages > Data Exchange Package - Microsoft Excel
Data Exchange Package - Microsoft Excel
print icon

Overview

The Data Exchange Package is an optional feature of Access It! allowing for importing and updating of the Cardholder database. The Data Exchange Package can be ran manually, or set to run at automatic intervals. The following Microsoft Excel Spreadsheet formats are supported by the Data Exchange Package:

  • Microsoft Excel 2003 Spreadsheet (*.xls)
  • Microsoft Excel 2007 / 2010 Spreadsheet (*.xlsx)

When executed, the Data Exchange Package will process all records from the source Microsoft Excel Spreadsheet. It is recommended that only required inserts, changes and deletions exist in the source Microsoft Excel Spreadsheet so that the entire cardholder population is not affected each time the package is run. This is typically accomplished through the use of a temporary Spreadsheet and configuring the Data Exchange Package to delete all records from the source each time the package is run.

Beginning in Access It! v5.2, a pre-created Data Exchange Package can be exported and imported into a new package. See this article for more information. 

Access It! Licensing
The following licensing option must be enabled within the Access It! dongle and is a system wide license.

  • Data Exchange Packages - Yes

Creating New Data Exchange Package

  1. Within Access It! navigate to Configuration | Data Exchange Packages
  2. Select New from the toolbar
  3. Assign a friendly name for this Data Exchange Package
  4. Select Import – Microsoft Excel from the Package Type drop down list

Data Source Tab (required)

  1. In the File name field, click Select and browse for the supported Microsoft Excel Spreadsheet the Data Exchange Package will import from
    The file to be imported must be located on the application server (the machine with the dongle on it).
  2. If the first column of the Excel Spreadsheet contains column headers, check the box First row contains column headers
  3. If the Excel Spreadsheet should be deleted after importing, check the box Delete file after execution

Field Mappings Tab (required)

  1. Select Add
  2. In the Destination drop down list, select the field in the Access It! database the import data will be placed into
  3. In the Source section, select the field from the Microsoft Excel Spreadsheet that will be mapped to the previously selected destination field. If a static value is to be assigned to the Destination field, select Static Value and enter accordingly

Advanced Mapping Options (optional)

Advance mapping allows for transforming the import data when the Data Exchange Package is executed.

Input Transform (optional)

Input Transform allows for using a certain portion of the imported field. An example of when this option would be used is when the imported field is a person’s social security number, and only the last 4 digits are required to be imported.

  1. Select Use part of value(Substring)
  2. Select which character you wish the data field to begin at
    If all of the data is needed after the starting point, select All remaining characters.
    If only a certain number of characters are needed, select Character Count and set as needed.

Output Transform (optional)

Output Transform allows for modifying the case format of the imported field. An example of when this option would be used is when source data is formatted in all caps, and should be imported using proper case formatting.

  1. Select None to leave the formatting as is
  2. Select To Upper Case to force the source data to be imported in all upper case
  3. Select To Lower Case to force the source data to be imported in all lower case
  4. Select To Proper Case to force the source data to be imported in all proper case
  5. Select Format value to specify a custom format string that will be used to format the import source

Value Mappings (optional)

Value Mappings allow for mapping a value in the source field to a custom output value which will be imported. An example of when this option would be used is when the imported data is stored as a number value and should be mapped to a friendly name during the import.
If the source mapping is an empty string, leave the input value mapping empty.

  1. Select Add
  2. In the Input Value field, select the value of the imported field that requires mapping
  3. In the Maps to Output Value, enter the desired output that will be imported when the input value criteria is matched.

Prepend / Append (optional)

Prepend / Append allows for custom data to be placed prior or after the imported source field.

  1. In the Prepend Text field, enter the data that will be placed prior to the import field
  2. In the Append Text field, enter the data that will be placed after the import field

Package Options Tab (required)

  1. In the Exception Logging Mode select whether to log import exceptions to a folder or to a File
  2. Select the path the import exceptions will log to
    If no path is specified, the default path is .\ProgramData\RS2 Technologies, LLC\Access It! Universal.
  3. Within the Key Field drop list, select a unique key field. The Key field is used to compare existing data to the imported data and then used to import a new record, or update the existing field
  4. Set Card Modification Rule accordingly
    The Card Number field must be mapped within the Field Mappings tab in order for the following rules to apply.
    1. Insert / Update Card
      • When the Key field between source and destination match, the existing card record will be updated, or created if not already existing
    2. Replace Existing Card(s)
      • When the Key field between source and destination match, the existing card record will be replaced
    3. Deactivate Existing Card(s)
      • When the Key field between source and destination match, the existing card record will deactivated
  5. Set Access Level Modification Rule accordingly
    One or more Access Levels need to be mapped within the Field Mapping tab in order for the following rules to apply.
    1. Insert / Update Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are not altered and the new Access Level is inserted to the card.
    2. Replace Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the card and then replaced with the new Access Level imported.
    3. Delete Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the card.
    4. Insert / Update Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are not altered and the new Access Level is inserted on the Cardholder Level.
    5. Replace Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the cardholder and then replaced with the new Access Level imported.
    6. Delete Cardholder Access Levels
      • When the Key field between source and destination match, the existing Access levels assigned are removed from the cardholder.
  6. Set the Access Level creation rule accordingly
    Option available beginning in Access It! Universal.NET V5.5
    1. Don't Create
      • If the access level in the imported source does not exist within Access It! it will not create a new access level.
    2. Create in specific sites
      • If the access level in the imported source does not exist within Access It! it will create a new access level for each site specified. To specify a site, click the Specify button.
    3. Create in all sites
      • If the access level in the imported source does not exist within Access It! it will create a new access level within every site.
  7. If the imported data should be created if it does not exist in the destination, check the box Insert record if it does not exist
  8. When the Delete Control Field matches the Delete Control Value select whether the Cardholder or the Card should be deleted
  9. Within the Delete Control Field, select the field from the import source that will be compared against the Delete Control Value. If the values match, the record will be deleted
  10. Within the Delete Control Value, enter a value that will be compared against the Delete Control Field. If the values match, the record will be deleted

Pre/Post-processing steps Mappings Tab (optional)

Pre/Post steps allow for the Data Exchange Package to execute a program, or batch file before or after executing

  1. In the Preprocessing Step field, enter a valid file path or click Add Step and browse for an executable file to be ran before the Data Exchange package executes
  2. In the Postprocessing Step field, enter a valid file path or click Add Step and browse for an executable file to be ran after the Data Exchange package executes

Schedule Tab (optional)

  1. From the Schedule Type drop list, select the type of schedule determine how often the Data Exchange Package will automatically execute
  2. Assign the Start Date/Time accordingly

Executing a Data Exchange Package

  1. Within Access It! navigate to Configuration | Data Exchange Packages
  2. Select the Data Exchange package to be executed
  3. From the toolbar, select Execute Data Exchange Package

Mobile Credentials

The following articles outline the specifics required when importing mobile credentials for each manufacturers respectfully.

More Information

When trying to import numerical values with leading zeroes see Flat File Schema Formatting for more information.


scroll to top icon