Data Exports

Introduction to Data Exports

It is the current business process to request data exports on a scheduled basis from the complaint and case management systems:

• CEEB: Quarterly Exports on the first day of the new quarter for the previous quarter's data
• COS: Monthly Exports around the middle of the month for the previous month's data

This process may be replaced in the future with self service access via Metabase. This page will describe the location of the exports and the process for running and distributing them.

Location of Exports

The data exports can be found in the /export directory in both the nr-compliance-enforcement and nr-compliance-enforcement-cm folders/repos

Connecting to the Production Database

See https://github.com/bcgov/nr-compliance-enforcement/wiki/Openshift

Export of Scripts

COS

NatCom:

• Execute cos_hwcr_complaint_export.sql
• Execute cos_ers_complaint_export.sql

CM:

• Execute cos_assessment_export.sql
• Execute cos_hwcr_case_export.sql
• Execute cos_hwcr_equipment_export.sql

After each script is run export the results to a CSV File.

CEEB

NatCom:

• Execute ceeb_complaint_export.sql

CM:

• Execute ceeb_quarterly_case_export.sql

After each script is run export the results to a CSV File.

Note the following settings:

• Encoding should be UTF-8
• Timestamp pattern should be yyyy-MM-dd HH:mm:ss
• Insert BOM should be true

In DBeaver these are set as part of the export wizard:

Manual Adjustments to Export

COS

At this point you will have 5 CSV files. The goal is to get them into a single XLSX file and to clean up each sheet a bit.

• Open the HWCR csv file.
• Apply Bold Formatting to the header row.
• Resize all the columns so that the whole thing is readable. This can be done easily by highlighting all columns by clicking at the top left of the sheet, and then double clicking between column A and B

• Format the datetime column (Column F 'Date Logged (PDT/PST)') to be of type Custom in the format YYYY-MM-DD hh:mm
• Format the datetime column (Column G 'Incident Datetime (PDT/PST')) to be of type Custom in the format YYYY-MM-DD hh:mm

• Format the date column (Column M 'Update Logged Date') to be of type Date in the format YYYY-MM-DD

• Add each csv as a tab to the hwcr csv file. You should end up with 5 tabs.
• The ERS tab will require all of the formatting above to be applied, and the remaining tabs will just require the header and column resizing updates
• Convert the results to an XLSX File as NatComExport__.xlsx
• Password Protect the XLSX File (See Alec, Neil or Miwa for the scheme).

CEEB

• Run the merge_exports.py script located in the exports folder to merge both the complaint and the case management exports together.
• Convert the results to an XLSX File.
• Password Protect the XLSX File (See Alec, Neil or Miwa for the scheme).
• Resize all the columns so that the whole thing is readable. This can be done easily by highlighting all columns by clicking at the top left of the sheet, and then double clicking between column A and B. Note the Alleged Contravener column will need to be shrunk as it free form text.
• Save the file as __NatCom_Export.xlsx

Delivery of Export

• Once the file has been password protected it can be emailed.