Using the Database Importer (Beta)

The Database Importer in OPSCOM provides administrators with a powerful tool to import existingbulk data into the system using CSV files. ThisIts functionalityprimary purpose is essentialto forfacilitate initial data migration,migrations, execute mass record updates, orand integratingintegrate data from external systems,systems ensuringto thatensure your OPSCOM database is populated and maintained efficiently. This toolarticle is currentlyintended infor BETA.OPS-COM administrators responsible for backend data management.

Setup &and Configuration

This

1. Hoverfeature overrequires Tools,specific thenadministrative click Data Importer - BETApermissions to access theand tool.

Permissions Requirements

Ifoperate the import tools.

DataAdmin ImporterSide: pageAdministrators is not visible, the user's account does notmust have the necessaryappropriate permissions enabled.enabled Tounder allowthe aSystems tab of the permissions management page. For complete instructions on modifying user access, refer to import or manage data, the followingManage permissionsRoles mustand bePermissions configured:wiki article.

• Manage tablestables:: This permission allowsEnables the user to view the Managemanage Tablestables page and utilize the upload functionality for importing data. It does NOTnot grant the ability to reset or purge tables.

• Reset tablestables:: This permission grantsGrants the ability to empty (reset)or reset tables. ItThis does NOT allowrequires the user to see the Manage Tables page unless Manage tables ispermission alsoto enabled.be enabled as well so the user can access the page.

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

TheAdministrators Databasecan Importeruse allowsthe 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 beaccess importedthe intomain amanagement select number of tables within OPSCOM.screen.

   Currently Supported Tables for Import

   • UserProfile
   • OffenceLocations

Vehicle

Permits

Violations

Importing Data into a Table

Note - There is a limit of 10000 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 tabletable.

   that you wish to import information into.
2. A modal

   Select windowyour willcomma-differentiated open,or prompting you to select the CSV file. Your importedsemi-colon-differentiated CSV file canfrom bethe comma-differentiatedmodal orwindow.

   semi-colon-differentiated.
3. Once a

   Click file has been selected, clickthe Submit button to begin the file upload process.and proceed to the table import screen.

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 matchMatch the columns from your imported CSV file to the corresponding destination columns inwithin the destinationsystem. table within OPSCOM.

• 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, theThe system will automatically pre-select thematches appropriateif matches.your CSV contains recognized column names.

• 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 clickClick the Process button,button youto willinitiate bethe redirectedbackground backimport 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 mainspecific pagetable whileyou wish to empty.

2. Type DELETE into the importconfirmation istext completedfield in the background.modal window.

   Post-Import

Processing and Settings

3. AfterClick the initialDelete import of data into the base table, some tables run additional processing on the informationbutton to establishpermanently relationshipspurge andall apply default settings. These processes run separatelyrecords from the initialselected import and are not affected by the duplicate settings chosen for the import.table.

   Status

Emails

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

• The first emailtable is sentan afterirreversible, permanent action. Once 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 sentdeleted, afterit thecannot databe hasrecovered. beenAdditionally, insertedpurging intoprimary thetables base(like tablevehicles andor 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 permitpermits) will automatically havepurge aclosely bookingconnected createdjoin fortables 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 OPSCOM.
  • Newly created users are automatically set to enabled.
• Vehicle:
  • If the user association column is matched, the created(like vehicle willjoins beor automaticallypermit associatedjoins).
    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 shouldmust generally be doneexecuted in a specific order,order asto someensure tablesdependencies containare informationmet that references another table. While imports can be done out of order,and records mayassociate 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.correctly.

User-AssociationDuplicate Settings

SomeWhen tablesimporting include additional post-processing to associate newly-created records with existing users. For this to work,data, the system uses specific 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'srecord informationalready exists. If a match is unique. Iffound, the information in these columns is duplicated in the file or already exists in the system, itsystem will be handled based onfollow your selected Duplicateduplicate Settings:

setting

to
• Ignoringeither a duplicate: The system will do nothing withignore the duplicate record.
(do
• Overwritenothing) existing:or The information in the new record will replaceoverwrite the existing record with the samenew uniqueCSV identifier.

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

• UserProfileUserProfile:: Uses one identifier, which can be either UserUUID or Email. If both are supplied, UserUUID takes precedence.

• OffenceLocationsOffenceLocations:: Uses one identifier, which is LocationName.

• VehicleVehicle:: Uses one identifier, which is Plate.

• PermitsPermits:: Uses two primary identifiers:both PermitNo and LotNameID. Both are required to make a unique record,required, meaning identical PermitNopermit valuesnumbers can exist if they haveare assigned to different LotNameIDlots.

  values.

• ViolationsViolations:: 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

Processing

InAfter additiondata is inserted into the base table, the system runs separate, automated post-processing to importingestablish 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. relationships.

   • IrreversibleStatus Action:Emails: ThisYou actionwill isreceive permanent.a Oncefirst email confirming the datarows 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)inserted into the confirmationtemporary text fieldtable, and pressa second email detailing the Deletefinal button.records updated, inserted, and post-processed.

4. ThePermits: Permits are automatically booked to associated users. If selected tableduring import, these permits will thenautomatically be purgedmarked ofas allpaid; records.

   otherwise,

Related Table Purges

Some tablesthey are closely connectedsent to the recordsuser'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 a related table. Removing the data they contain will also purge the contents of the related table:import.

• 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 &and Considerations

• DataPreserve Preparationleading iszeros Keyin Excel:: EnsureForce 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 CSVoriginal filesheet is(e.g., meticulously="'"&A1). prepared.Drag Accuratethe data,formula correctacross formatting,your and adherencedata to specifiedadd columnan namesapostrophe (ifin known)front willof significantlyall reducevalues, importthen errors.save this new sheet as your CSV.

• BackupAlign Beforeuser Importassociation identifiers:: WhileEnsure notyou explicitlyselect athe systemexact feature,same it'sunique aidentifier best(UserUUID practiceor 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 ensure you have recent system backups before performing large-scale imports or table resets.fail.

• UnderstandReview Dependenciescolumn 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 "OrderImporter ofField Operations"Descriptions beforeguide importing, especially when dealing with related tables like UserProfile, Vehicles, and Permits. Importing out of order can leadprior to unassociated records.uploading.

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