Skip to content

Data Exports

Jump to bottom
afwilcox edited this page Dec 18, 2024 · 8 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.

The contents of this page contains information about complaint exports only. For case management exports see: https://github.com/bcgov/nr-compliance-enforcement-cm/wiki/Data-Exports

Location of Exports

The data exports can be found in the /export directory

Connecting to the Production Database

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

Export of Scripts

COS

  • Execute cos_hwcr_complaint_export.sql
  • Export the results to a CSV File.

CEEB

  • Execute ceeb_complaint_export.sql
  • Export the results to a CSV File.

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