Colorado Technical Information

General

CSVs are copied to the server. Their group and ownership are changed to 'mysql' using the chgrp and chown commands. They are then moved to /var/lib/mysql/wellsdb_dev/ such that they can be read by mysql.

CSVs should be checked for \n \ and other special characters prior to being uploaded to the server. " are okay if they are surrounding an entire field value, but care must be taken that none are unintentionally present or their count is not odd. Special characters can cause the files to be processed incorrectly, which leads to data going into the wrong columns or not being uploaded at all.

Always make backups prior to destructive operations.

Production

The table production_co was created to contain the CO production data. Many IF conditionals were used in the import to prevent fields from being incorrectly set to 0, "", or "0000-00-00" instead of NULL. In total, 17 files were imported. Each contained the production data for one year in the range 1999-2015.

Importing:

LOAD DATA INFILE 'monthly_prod.csv'

INTO TABLE production_co

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

(@MONTH_TMP, @YEAR_TMP, @API_STATE_TMP, WELL_API_COUNTY_ID, @API_SEQ_TMP, API_SIDETRACK, FORMATION_CODE, WELL_STATUS_CODE, @PROD_DAYS_TMP, @WATER_DISP_CODE_TMP, @WATER_VOL_BBLS_TMP, @WATER_PRESS_TUBING_PSIA_TMP, @WATER_PRESS_CASING_PSIA_TMP, @BOM_INVENTORY_BBLS_TMP, @OIL_VOL_BBLS_TMP, @OIL_SALES_BBLS_TMP, @OIL_VOL_ADJUSTED_BBLS_TMP, @EOM_INVENTORY_BBLS_TMP, @GRAVITY_SALES_TMP, @GAS_SALES_MCF_TMP, @GAS_FLARED_MCF_TMP, @GAS_VOL_LEASE_MCF_TMP, @GAS_SHRINKAGE_MCF_TMP, @GAS_VOL_MCF_TMP, @BTU_SALES_TMP, @GAS_PRESS_TUBING_TMP, @GAS_PRESS_CASING_TMP, OPERATOR_NUM, OPERATOR_NAME, WELL_NAME, @WELL_NUM_TMP, @ACCEPTED_DATE_TMP, IS_REVISED)

SET PERIOD_ID=CONCAT(@YEAR_TMP,"-",@MONTH_TMP),

WELL_API=CONCAT(@API_STATE_TMP,"-",WELL_API_COUNTY_ID,"-",@API_SEQ_TMP),

PROD_DAYS=IF(@PROD_DAYS_TMP="",NULL,@PROD_DAYS_TMP),

WATER_DISP_CODE=IF(@WATER_DISP_CODE_TMP="",NULL,@WATER_DISP_CODE_TMP),

WATER_VOL_BBLS=IF(@WATER_VOL_BBLS_TMP="",NULL,@WATER_VOL_BBLS_TMP),

WATER_PRESS_TUBING_PSIA=IF(@WATER_PRESS_TUBING_PSIA_TMP="",NULL,@WATER_PRESS_TUBING_PSIA_TMP),

WATER_PRESS_CASING_PSIA=IF(@WATER_PRESS_CASING_PSIA_TMP="",NULL,@WATER_PRESS_CASING_PSIA_TMP),

BOM_INVENTORY_BBLS=IF(@BOM_INVENTORY_BBLS_TMP="",NULL,@BOM_INVENTORY_BBLS_TMP),

OIL_VOL_BBLS=IF(@OIL_VOL_BBLS_TMP="",NULL,@OIL_VOL_BBLS_TMP),

OIL_SALES_BBLS=IF(@OIL_SALES_BBLS_TMP="",NULL,@OIL_SALES_BBLS_TMP),

OIL_VOL_ADJUSTED_BBLS=IF(@OIL_VOL_ADJUSTED_BBLS_TMP="",NULL,@OIL_VOL_ADJUSTED_BBLS_TMP),

EOM_INVENTORY_BBLS=IF(@EOM_INVENTORY_BBLS_TMP="",NULL,@EOM_INVENTORY_BBLS_TMP),

GRAVITY_SALES=IF(@GRAVITY_SALES_TMP="",NULL,@GRAVITY_SALES_TMP),

GAS_SALES_MCF=IF(@GAS_SALES_MCF_TMP="",NULL,@GAS_SALES_MCF_TMP),

GAS_FLARED_MCF=IF(@GAS_FLARED_MCF_TMP="",NULL,@GAS_FLARED_MCFS_TMP),

GAS_VOL_LEASE_MCF=IF(@GAS_VOL_LEASE_MCF_TMP="",NULL,@GAS_VOL_LEASE_MCF_TMP),

GAS_SHRINKAGE_MCF=IF(@GAS_SHRINKAGE_MCF_TMP="",NULL,@GAS_SHRINKAGE_MCF_TMP),

GAS_VOL_MCF=IF(@GAS_VOL_MCF_TMP="",NULL,@GAS_VOL_MCF_TMP),

BTU_SALES=IF(@BTU_SALES_TMP="",NULL,@BTU_SALES_TMP),

GAS_PRESS_TUBING=IF(@GAS_PRESS_TUBING_TMP="",NULL,@GAS_PRESS_TUBING_TMP),

GAS_PRESS_CASING=IF(@GAS_PRESS_CASING_TMP="",NULL,@GAS_PRESS_CASING_TMP),

WELL_NUM=IF(TRIM(@WELL_NUM_TMP)="",NULL,@WELL_NUM_TMP),

ACCEPTED_DATE=IF(@ACCEPTED_DATE_TMP="",NULL,@ACCEPTED_DATE_TMP)

Instances of IS_REVISED that were not "Y" were set to NULL following import of all years' data:

UPDATE production_co

SET IS_REVISED=NULL

WHERE IS_REVISED<>"Y"

The WELL_COUNTY and WELL_STATUS fields were finally populated using fill_long_fields.py in ~/installers/wikimanager/ (see script for usage instructions).

Permit

To populate county names in CSV before import, the following formula was used:

"=VLOOKUP(A3,[API_County_codes.xlsx]Sheet1!$A$1:$B$64,2,FALSE)" and fill down column

• where A3 contained county code in main sheet

• In API_County_codes, sheet 1, county codes were in column A and county names were in column B

• county names were added starting at B3 in main sheet

Importing:

LOAD DATA INFILE 'Wells_proper_fields.csv'

INTO TABLE permit

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 2 ROWS

(WELL_COUNTY, @DUMMY, OPERATOR_NAME, WELL_API, WELL_API_COUNTY_ID, FARM_NAME, SPUD_DATE, LATITUDE_DECIMAL, LONGITUDE_DECIMAL, OPERATOR_OGO, @DUMMY, @DUMMY, @DUMMY, @DUMMY, @DUMMY)

SET WELL_STATE_CODE='CO', WELL_COUNTRY='United States'