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:

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
Importing Data into a Table

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.

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:

Table-Specific Post-Processing Details
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.

Table to Import Requires (Imported Before) Notes
UserProfile (None) This should generally be the first table imported.
Vehicles UserProfile Having the user record created before the vehicle allows the user to be associated with the vehicle by a VehicleJoin record created during post-import processing.
OffenceLocations (None) Does not require any data beforehand but should be imported before Violations to ensure proper location marking.
Permits Vehicles, UserProfile Having a vehicle record created before the permit allows the permit to be associated with the vehicle by a PermitJoin record created during post-import processing. If there is also an associated user record, the permit can be marked as paid by joining the user and permit through a payment record (if the option was selected).
Violations Vehicles, UserProfile, OffenceLocations A violation requires the existence of a vehicle record beforehand for the violation to be created at all. Offence locations should be imported before violations for the violation to have its location properly marked.
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:

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:

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

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).

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

Inserting Into Table Column Inserting Into Sourced From Table
UserProfile prov Provinces
  UserTypeID UserTypes
Vehicle ProvID Provinces
  ColourID VehicleColours
  MakeID VehicleMake
  TypeID VehicleType
  PlateTypeID VehiclePlateType
Permits LotNameID LotName
  StateID PermitState
Violations VehicleID Vehicle
  TicketType TicketCategory
  LocationID OffenceLocations
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.

    <callout type="warning" title="Irreversible Action"> This action is permanent. Once the data is deleted, it cannot be recovered. Ensure you are absolutely certain before proceeding. </callout>

  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.

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:

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


Best Practices & Considerations


Revision #15
Created 30 April 2024 08:04:21
Updated 17 June 2025 08:29:43 by Cedar Boulianne