Using the Database Importer (Beta)

The Database Importer in OPS-COM provides administrators with a powerful tool to import existing data into the system using CSV files. This functionality is essential for initial data migration, mass updates, or integrating data from external systems, ensuring that your OPS-COM database is populated and maintained efficiently. This tool is currently in BETA.

Setup & Configuration

1. Click Tools, then Database Importer to access the tool.

Permissions Requirements

If the Database Importer page is not visible, the user's account does not have the necessary permissions enabled. To allow a user to import or manage data, the following permissions must be configured:

• Manage tables: This permission allows the user to view the Manage Tables page and utilize the upload functionality for importing data. It does NOT grant the ability to reset tables.
• Reset tables: This permission grants the ability to empty (reset) tables. It does NOT allow the user to see the Manage Tables page unless Manage tables is also enabled.

These permissions can be found under the Systems tab of the permissions management page. Enable them to grant a user access to the Database Importer functionality. For more information on configuring permissions, please refer to the User Roles and Permissions wiki article.

Using this Feature

The Database Importer allows data to be imported into a select number of tables within OPS-COM.

Currently Supported Tables for Import

• UserProfile
• OffenceLocations
• Vehicle
• Permits
• Violations

Importing Data into a Table

Note - There is a limit of 65000 records to be imported in one batch. This includes the header row.

To begin a new import:

1. Click the Upload button next to the desired destination table that you wish to import information into.
2. A modal window will open, prompting you to select the CSV file. Your imported CSV file can be comma-differentiated or semi-colon-differentiated.
3. Once a file has been selected, click Submit to begin the upload process.
4. After the CSV file has been successfully uploaded, the Table Import screen will appear.

Converting Fields to Text to Avoid Truncating Leading Zeros - When converting data from Excel to CSV, issues can arise, such as the truncation of leading zeros in numbers (e.g., student IDs, staff numbers). To prevent this, you can force Excel to treat cells as text before converting to CSV as below:

1. Open a new sheet in your Excel workbook.
2. In cell A1 of the new sheet, type the formula: ="'"& then click on cell A1 of your original spreadsheet. (This formula is: equals sign, double quote, apostrophe, double quote, ampersand, then the cell reference.)
3. Drag this formula down and across through the same number of rows and columns as your original data. This will replicate your data on the new page, but an apostrophe (') will be placed in front of all values, forcing Excel to treat them as text.
4. Save the new sheet as a .CSV file. Ensure you only save the new page. This method effectively preserves leading zeros during the CSV conversion.

Column Matching

On the Table Import screen, you will match the columns from your imported CSV file to the corresponding columns in the destination table within OPS-COM.

• This step informs the program where each piece of data from your CSV file belongs in the destination table.
• If your CSV file contained recognized column names, the system will automatically pre-select the appropriate matches.
• For detailed information on each column's purpose, whether it is required or optional, and specific formatting, please refer to the Importer Field Descriptions guide.

Once the columns have been properly matched and you click the Process button, you will be redirected back to the main page while the import is completed in the background.

Post-Import Processing and Settings

After the initial import of data into the base table, some tables run additional processing on the information to establish relationships and apply default settings. These processes run separately from the initial import and are not affected by the duplicate settings chosen for the import.

Status Emails

Over the course of the import process, a total of two emails will be sent to the user who initiated the import:

• The first email is sent after the data from the CSV has been inserted into the temporary table. It records the number of rows that were successfully imported and the rows that failed due to being malformed.
• The second email is sent after the data has been inserted into the base table and post-processing is complete. It contains the number of records that were updated, the number of records inserted, and any relevant information from the post-import processing that occurred.

Table-Specific Post-Processing Details

• Permits:
  • If the user association column is matched, the created permit will automatically have a booking created for the associated user via a PermitJoin record.
  • If the option was selected during import, newly-created permits booked to users will be automatically marked as paid. Otherwise, they will be located in the users' carts, requiring users to complete payment.
• UserProfile:
  • When users are imported, if a LoginSource is not provided, it will be automatically set to OPS-COM.
  • Newly created users are automatically set to enabled.
• Vehicle:
  • If the user association column is matched, the created vehicle will be automatically associated with the user via a VehicleJoin record.
  • If the alert column is matched, an alert will be created and automatically attached to the associated vehicle via an AlertComments record. The vehicle will also be flagged. The list of vehicles being marked with alerts may also be called a hotlist.
• Violations:
  • Any violations created that do not have an Issued Date and Due Date will have one automatically generated for them at the time of import.
• OffenceLocations:
  • This table has no specific post-processing.

Order of Operations for Related Imports

Imports that contain related information should generally be done in a specific order, as some tables contain information that references another table. While imports can be done out of order, records may not be associated correctly if their dependencies aren't met. In general, the tables a record requires should be imported before that record's table.

User-Association Settings

Some tables include additional post-processing to associate newly-created records with existing users. For this to work, the unique identifier for the user (UserUUID or Email) must be selected consistently for both the user's primary record and any associated records (Vehicles, Permits). Ensure the same value is selected for both sections during the import setup.

The tables that currently have this user-association option are:

• Vehicles
• Permits

Unique Identifiers & Duplicate Settings

The Unique Identifiers are the columns used to determine if a record's information is unique. If the information in these columns is duplicated in the file or already exists in the system, it will be handled based on your selected Duplicate Settings:

• Ignoring a duplicate: The system will do nothing with the duplicate record.
• Overwrite existing: The information in the new record will replace the existing record with the same unique identifier.

The number of columns that mark a record as unique varies by table:

• UserProfile: Uses one identifier, which can be either UserUUID or Email. If both are supplied, UserUUID takes precedence.
• OffenceLocations: Uses one identifier, which is LocationName.
• Vehicle: Uses one identifier, which is Plate.
• Permits: Uses two primary identifiers: PermitNo and LotNameID. Both are required to make a unique record, meaning identical PermitNo values can exist if they have different LotNameID values.
• Violations: Uses one identifier, which is Ticket.

Foreign Lookup Columns

Some data in one table originates from another table and is stored as an ID in the destination table (known as a foreign key).

• Data in these columns should be entered as normal text in your CSV. The system will automatically look for a match in the corresponding lookup table.
• If a matching value is found (e.g., a "Colour" name in the VehicleColour table), the system will input the correct ID value into the destination table.
• If no matching value is found in the lookup table, the system will enter a Null value instead.

Below is a list of common foreign lookup columns and their source tables:

Table Reset

In addition to importing data, the Database Importer page also allows for the purging of an entire table's contents.

1. Click the Reset button next to the table you wish to empty.

2. A new modal window will open, prompting you to confirm that you wish to delete the contents of the table. 

   • Irreversible Action: This action is permanent. Once the data is deleted, it cannot be recovered. Ensure you are absolutely certain before proceeding.
     

3. If you are certain the data should be deleted, type DELETE (in all caps) into the confirmation text field and press the Delete button.

4. The selected table will then be purged of all records.

Related Table Purges

Some tables are closely connected to the records of a related table. Removing the data they contain will also purge the contents of the related table:

• Purging the Vehicle table will also purge the VehicleJoin table.
• Purging the Permits table will also purge the PermitJoin table.

Note: Only the table contents are deleted; the table structure itself remains intact.

Best Practices & Considerations

• Data Preparation is Key: Ensure your CSV file is meticulously prepared. Accurate data, correct formatting, and adherence to specified column names (if known) will significantly reduce import errors.

• Backup Before Import: While not explicitly a system feature, it's a best practice to ensure you have recent system backups before performing large-scale imports or table resets.

• Understand Dependencies: Always review the "Order of Operations" before importing, especially when dealing with related tables like UserProfile, Vehicles, and Permits. Importing out of order can lead to unassociated records.

• Handle Duplicates Strategically: Choose your "Duplicate Settings" (Ignore or Overwrite) carefully based on whether you intend to add new unique records or update existing ones.

• Monitor Status Emails: Pay close attention to the status emails sent during the import process. They provide crucial feedback on success rates and potential issues.