Skip to content

Data Exports

Jump to bottom
afwilcox edited this page Jul 18, 2025 · 13 revisions

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:

image

Manual Adjustments to Export

COS

  • Convert the results to an XLSX File.
  • Password Protect the XLSX File (See Alec or Miwa for the scheme).
  • Format the date column (Column C 'Date Logged) to be of type Date in the format YYYY-MM-DD

image

  • Format the datetime column (Column D 'Incident Datetime) to be of type Custom in the format YYYY-MM-DD hh:mm

image

  • 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

image

  • Save the file as Complaint data.xlsx

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 (Scheme TBD).
  • 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.
Clone this wiki locally