# Using the Database Importer (Beta)

The **Database Importer** in OPSCOM 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 OPSCOM database is populated and maintained efficiently. This tool is currently in **BETA**.

### Setup & Configuration 1. Hover over **Tools**, then click **Data Importer - BETA** to access the tool. ##### Permissions Requirements If the **Data 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.](https://opscom.wiki/books/setup-configuration-for-admins/page/manage-roles-and-permissions) --- ### Using this Feature The **Database Importer** allows data to be imported into a select number of tables within OPSCOM. ##### 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 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 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, 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](https://opscom.wiki/books/for-administrators-staff/page/importer-field-descriptions). 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 **OPSCOM**. - 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.
Table to ImportRequires (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: - **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:
Inserting Into TableColumn Inserting IntoSourced 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. -

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