Skip to content

Data Exports

Jump to bottom
afwilcox edited this page Dec 18, 2024 · 4 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 case exports only. For complaint management exports see: https://github.com/bcgov/nr-compliance-enforcement/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_case_export.sql
  • Export the results to a CSV File.

CEEB

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

Note: Ensure that the 'Insert BOM' option is checked to provide proper encoding

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 G 'Outcome date) to be of type Date in the format YYYY-MM-DD

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 Case data.xlsx

CEEB

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

Delivery of Export

  • Once the file has been password protected it can be emailed.
Clone this wiki locally