Documentation
Database Bulk Change
Database Import Version 1 Last Review Date: Feb, 2025

How do we bulk import some info?

Users receive the file

    Users will need to be provided with the latest version of the Bulk Import File.     Currently, it's version 25 found in *...\Data Import\1. Master\Scripts 25 (latest)* folder in Onedrive.

   

        # Importing Work Orders         Bulk Import File: CMMS WO Import Sheet v25.xlsx    
   

Filling out the document

    Users will need to fill out the document.        

Select what the intention is for the bulk import?

   

        # Importing Work Orders         | Column name             | Explanation              |         | ----------------:       | :----------------------- |         | Site Name           | The location of the job. |         | Description         | What text should appear as the description of the works. |         | Service Provider    | Name of the provider.     |         | Work Order Type     | Work Orders require a type. |         | Work Order Subclass | Work Orders require a Subclass. |         | Priority            | Priority determines the urgency of the job and other time related fields. |         | Requestor           | Who is requesting the work. |         | Commence Before     | When should the work have been started before. |         | Complete By         | When the work should have been completed by. |         ### Contingent Columns         | Column name       | Explanation                               |         | ----------------: | :---------------------------------------- |         | Service Types | Required if set by the contract settings. |    
                       

File Review

Before we review the file we need to do some additional set up. Access the "...\Data Import" folder and locate the specific contract folder. Create a folder inside the contract folder with the appropriate title. (E.G. Work Orders - 2025-02 Feb)

This folder will be referred to as "Working Directory" Paste the Bulk Import File into the Working Directory. Copy the folder "...\Data Import\1. Master\Scripts 25 (latest)" into the Working Directory.

Now open and review the file.

Checks to perform for all bulk imports

Check 1 - Script column

Find the Script column, it will be the right most column of the Excel sheet. Sometimes the column is named "Generated SQL - insert to staging tables". Click on the first cell containing SQL, Then in Excel's Formula bar, This will highlight the row each column is using. We need to confirm that the script is only using data from the same row.

Check 2 - Syntactic check

If a Column requires reference to some information from CMMS (For example 'Work Order Type') the text entered into the spreadsheet needs to match exactly.

  • Check Spelling
  • Check Capitalisation

If the text does not match exactly then when validating the information in SQL, the test run of the scripts will return an error.

For specific bulk imports check for the following

   

        # File Review for Work Orders         ### Column Review         | Column name          | Check |         | -------------------: | :---- |         | Service Types    | Any service types added may not exist in the contract. |         | Work Order Types | Some contracts do not have Scheduled Maintenance. |         | Commence Before  | Make sure that the date and time formatting is correct. |         | Commence Before  | Make sure that the date and time formatting is correct. |    
                       

Validate the file

Connect to the CBC SQL database and open "excel-import.sql" and "excel-import-stage.sql". Swap the connection from Master to cmms-prod. To validate the file in SQL, copy the Script column data out of the Import Excel and paste the information into a blank Microsoft Word document. Once copied, you can confirm the data is correct by checking the ID value in the final row in the Word document. Now copy the text from the Microsoft Word document at the bottom of the "excel-import-stage.sql". In "excel-import-stage.sql" Update the values for the following rows to be correct.     1. stgConfig_Client(ID)     2. stgConfig_Contract(ID)     3. setvar path to have the correct path for the Working Directory\scripts. Finally, we need to make sure setvar test is set to 1, if it is set to 0 it will attempt to import directly into the database. Now click “Execute”

If this test has been successful you will see the message "DATA WOULD BE IMPORTED SUCCESSFULLY. ROLLING BACK NOW, SINCE IN TEST MODE..."

Get approval

Before importing directly into the database you will need to get approval from another user with database approval. At this time Bryce is the only person able to approve database bulk imports.

Complete the import

Once all the above steps have been performed, update "excel-import.sql" setvar test to 0 and Execute the SQL script.

Documentation Update ProcedureLocal Setup