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 numeric fields from being incorrectly set to 0 instead of NULL.

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, @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, ACCEPTED_DATE, 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_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)