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.

PERMIT:

Cleared out old data since new data is much more extensive:

DELETE FROM permit

WHERE WELL_API LIKE '47%'

Populating permit table from owncomp:

Note: Duplicate rows were first removed using Excel. Duplicate rows occurred because, while all rows in owncomp were distinct, only a subset of the columns of owncomp were taken for the permit table. In some cases, the only distinguishing values between two rows occured in the ommitted columns. Deleting duplicates reduced the number of rows from 156,674 to 155,242 (a difference of 1,432).

LOAD DATA INFILE 'Wv_permit.csv'

INTO TABLE permit

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(@WELL_API_TMP, WELL_COUNTY, DB_COMMENTS, OPERATOR_OGO, FARM_NAME, OPERATOR_NAME, @PERMIT_ISSUED_DATE_TMP, SPUD_DATE, WELL_TYPE, CONFIGURATION, proposed_total_depth)

SET WELL_API = CONCAT(SUBSTRING(@WELL_API_TMP,1,2),'-',SUBSTRING(@WELL_API_TMP,3,3),'-',SUBSTRING(@WELL_API_TMP,6,5)),

WELL_API_COUNTY_ID = SUBSTRING(@WELL_API_TMP,3,3),

PERMIT_ISSUED_DATE = DATE_FORMAT(STR_TO_DATE(CONCAT(

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,1,2),'-',

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,4,2),'-',

SUBSTRING(@PERMIT_ISSUED_DATE_TMP,7,2)),'%d-%m-%y'),'%Y-%m-%d'),

WELL_STATE_CODE='WV',

WELL_COUNTRY='United States';

Grabbing latitude and longitude from locs (a table is created for this to make operations easier and to make the lat/lon data available to be input into other tables' WV data as well):

CREATE TABLE wv_latlon_loader

(

WELL_API varchar(50),

LATITUDE_DECIMAL double,

LONGITUDE_DECIMAL double

);

LOAD DATA INFILE 'Wv_latlon.csv'

INTO TABLE wv_latlon_loader

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(@WELL_API_TMP, LATITUDE_DECIMAL, LONGITUDE_DECIMAL)

SET WELL_API = CONCAT(SUBSTRING(@WELL_API_TMP,1,2),'-',SUBSTRING(@WELL_API_TMP,3,3),'-',SUBSTRING(@WELL_API_TMP,6,5));

Update permit to include latitude and longitude values:

UPDATE permit, wv_latlon_loader

SET permit.LATITUDE_DECIMAL = wv_latlon_loader.LATITUDE_DECIMAL, permit.LONGITUDE_DECIMAL = wv_latlon_loader.LONGITUDE_DECIMAL

WHERE permit.WELL_API = wv_latlon_loader.WELL_API;

SPUD

Populating spud table from owncomp:

As with permit, duplicate rows were deleted, reducing the number of records to insert from 156,674 to 154,521 (a difference of 2,153)

LOAD DATA INFILE 'Wv_spud.csv'

INTO TABLE spud FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(@WELL_API_TMP, WELL_COUNTY, OPERATOR_OGO, FARM_NAME, OPERATOR_NAME, SPUD_DATE, WELL_TYPE, CONFIGURATION)

SET WELL_API = CONCAT(SUBSTRING(@WELL_API_TMP,1,2),'-',SUBSTRING(@WELL_API_TMP,3,3),'-',SUBSTRING(@WELL_API_TMP,6,5)),

WELL_API_COUNTY_ID = SUBSTRING(@WELL_API_TMP,3,3),

WELL_STATE_CODE='WV',

WELL_COUNTRY='United States';

Update spud to include latitude and longitude values:

UPDATE spud, wv_latlon_loader

SET spud.LATITUDE_DECIMAL = wv_latlon_loader.LATITUDE_DECIMAL,

spud.LONGITUDE_DECIMAL = wv_latlon_loader.LONGITUDE_DECIMAL

WHERE spud.WELL_API = wv_latlon_loader.WELL_API;