Using the Database Importer (Beta)

The Database Importer provides administrators with a powerful tool to import bulk data into the system using CSV files. Its primary purpose is to facilitate initial data migrations, execute mass record updates, and integrate data from external systems to ensure your database is populated efficiently. This article is intended for OPS-COM administrators responsible for backend data management.

Setup and Configuration

This feature requires specific administrative permissions to access and operate the import tools.

Admin Side: Administrators must have the appropriate permissions enabled under the Systems tab of the permissions management page. For complete instructions on modifying user access, refer to the Manage Roles and Permissions wiki article.

• Manage tables: Enables the user to view the manage tables page and utilize the upload functionality for importing data. It does not grant the ability to reset or purge tables.

• Reset tables: Grants the ability to empty or reset tables. This requires the Manage tables permission to be enabled as well so the user can access the page.

Using this Feature

Administrators can use the following instructions to upload CSV files, match data columns, and initiate the import process.

Accessing the Importer

1. Hover over Tools and click Data Importer - BETA to access the main management screen.

Importing Data into a Table

1. Click the Upload button next to the desired destination table.

2. Select your comma-differentiated or semi-colon-differentiated CSV file from the modal window.

3. Click the Submit button to begin the file upload and proceed to the table import screen.

4. Match the columns from your imported CSV file to the corresponding destination columns within the system. The system will automatically pre-select matches if your CSV contains recognized column names.

5. Click the Process button to initiate the background import process.

The importer has a strict limit of 10,000 records per batch import. This maximum count includes your CSV header row.

Resetting a Table

The Database Importer also allows administrators to completely purge an entire table's contents.

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

2. Type DELETE into the confirmation text field in the modal window.

3. Click the Delete button to permanently purge all records from the selected table.

Resetting a table is an irreversible, permanent action. Once the data is deleted, it cannot be recovered. Additionally, purging primary tables (like vehicles or permits) will automatically purge closely connected join tables (like vehicle joins or permit joins).

Order of Operations for Related Imports

Imports that contain related information must generally be executed in a specific order to ensure dependencies are met and records associate correctly.

Table to Import	Requires Prior Import	Notes
UserProfile	None	This should generally be the first table imported.
Vehicles	UserProfile	Ensures the user record exists so the vehicle can associate via a join record.
OffenceLocations	None	Must be imported prior to violations for proper location marking.
Permits	Vehicles, UserProfile	Ensures vehicle and user records exist for permit associations and automated payment marking.
Violations	Vehicles, UserProfile, OffenceLocations	A violation requires an existing vehicle record to be created, and an existing location to be marked properly.

Unique Identifiers and Duplicate Settings

When importing data, the system uses specific unique identifier columns to determine if a record already exists. If a match is found, the system will follow your selected duplicate setting to either ignore the duplicate (do nothing) or overwrite the existing record with the new CSV data.

• UserProfile: Uses UserUUID or Email. If both are supplied, UserUUID takes precedence.

• OffenceLocations: Uses LocationName.

• Vehicle: Uses Plate.

• Permits: Uses both PermitNo and LotNameID. Both are required, meaning identical permit numbers can exist if they are assigned to different lots.

• Violations: Uses Ticket.

Inserting Into Table	Column Inserting Into	Sourced From Table
UserProfile	prov	Provinces
UserProfile	UserTypeID	UserTypes
Vehicle	ProvID	Provinces
Vehicle	ColourID	VehicleColours
Vehicle	MakeID	VehicleMake
Vehicle	TypeID	VehicleType
Vehicle	PlateTypeID	VehiclePlateType
Permits	LotNameID	LotName
Permits	StateID	PermitState
Violations	VehicleID	Vehicle
Violations	TicketType	TicketCategory
Violations	LocationID	OffenceLocations

Understanding Post Import Processing

After data is inserted into the base table, the system runs separate, automated post-processing to establish relationships.

• Status Emails: You will receive a first email confirming the rows inserted into the temporary table, and a second email detailing the final records updated, inserted, and post-processed.

• Permits: Permits are automatically booked to associated users. If selected during import, these permits will automatically be marked as paid; otherwise, they are sent to the user's cart.

• UserProfile: Users without a specified login source default to OPSCOM and are automatically set to enabled.

• Vehicle: Vehicles are automatically associated with matched users. Matched alerts will attach to the vehicle, flagging it on your hotlist.

• Violations: Violations missing an issued or due date will have one automatically generated at the time of import.

---

Best Practices and Considerations

• Preserve leading zeros in Excel: Force Excel to treat cells as text before converting to a CSV to prevent it from dropping leading zeros on IDs. Open a new sheet, type ="'"& and click the target cell from your original sheet (e.g., ="'"&A1). Drag the formula across your data to add an apostrophe in front of all values, then save this new sheet as your CSV.

• Align user association identifiers: Ensure you select the exact same unique identifier (UserUUID or Email) for both the primary user record and any associated records. Mixing identifiers between the user profile import and the vehicle/permit imports will cause post-processing associations to fail.

• Review column formatting thoroughly: Always cross-reference your CSV headers with the official data requirements. For detailed information on each column's purpose, whether it is required, and specific formatting rules, review the Importer Field Descriptions guide prior to uploading.