Importing CHIEF initial seed from CSV

sauliusg edited this page Feb 21, 2013 · 10 revisions

HMRC provided us with an Oracle .dmp.

This was imported into Oracle and then exported as CSV (using SQL Developer). Longer-term, we might want to use something like https://gist.github.com/3713603

Assuming there we have CHIEF CSVs exported from Oracle database (etariff binary file).

Warning! Use edit page view to copy these commands as github strips some formatting, notably double slash in 'ESCAPED BY '\'. Single slash in here results in an error in MySQL >= 5.6

LOAD DATA INFILE "TAMF.csv" INTO TABLE chief_tamf COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@fe_tsmp, msrgp_code, msr_type, @tty_code, @tar_msr_no, @dummy, @adval1_rate, @adval2_rate, @ai_factor, @cmdty_dmql, @cmdty_dmql_uoq, @cngp_code, @cntry_disp, @cntry_orig, @duty_type, @ec_supplement, @ec_exch_rate, @spcl_inst, @spfc1_cmpd_uoq, @spfc1_rate, @spfc1_uoq, @spfc2_rate, @spfc2_uoq, @spfc3_rate, @spfc3_uoq, @tamf_dt, @tamf_sta, @tamf_ty) SET fe_tsmp = STR_TO_DATE(@fe_tsmp, '%Y-%m-%dT%T'), tar_msr_no = REPLACE(@tar_msr_no, ' ', ''), adval1_rate = nullif(@adval1_rate, ''), adval2_rate = nullif(@adval2_rate, ''), spfc1_rate = nullif(@spfc1_rate, ''), spfc2_rate = nullif(@spfc2_rate, ''), spfc3_rate = nullif(@spfc3_rate, ''), ai_factor = nullif(@ai_factor, ''), cmdty_dmql = nullif(@cmdty_dmql, ''), cmdty_dmql_uoq = nullif(@cmdty_dmql_uoq, ''), cngp_code = nullif(@cngp_code, ''), cntry_disp = nullif(@cntry_disp, ''), cntry_orig = nullif(@cntry_orig, ''), duty_type = nullif(@duty_type, ''), ec_supplement = nullif(@ec_supplement, ''), ec_exch_rate = nullif(@ec_exch_rate, ''), spcl_inst = nullif(@spcl_inst, ''), spfc1_cmpd_uoq = nullif(@spfc1_cmpd_uoq, ''), spfc1_uoq = nullif(@spfc1_uoq, ''), spfc2_uoq = nullif(@spfc2_uoq, ''), spfc3_uoq = nullif(@spfc3_uoq, ''), tamf_dt = nullif(@tamf_dt, ''), tamf_ty = nullif(@tamf_ty, ''),  tty_code = nullif(@tty_code, ''), amend_indicator = 'I';

LOAD DATA INFILE "TAME.csv" INTO TABLE chief_tame COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@fe_tsmp, msrgp_code, msr_type, @tty_code, @tar_msr_no, @le_tsmp, @dummy, @adval_rate, @alch_sgth, @audit_tsmp, @cap_ai_stmt, @cap_max_pct, cmdty_msr_xhdg, @comp_mthd, @cpc_wvr_phb, @ec_msr_set, @ec_sctr, @mip_band_exch, @mip_rate_exch, @mip_uoq_code, @nba_id, @null_tri_rqd, @qta_code_uk, @qta_elig_use, @qta_exch_rate, @qta_no, @qta_uoq_code, @rfa, @rfs_code_1, @rfs_code_2, @rfs_code_3, @rfs_code_4, @rfs_code_5, @tdr_spr_sur, @exports_use_ind) SET fe_tsmp = STR_TO_DATE(@fe_tsmp, '%Y-%m-%dT%T'), tty_code = nullif(@tty_code, ''), le_tsmp = STR_TO_DATE(@le_tsmp, '%Y-%m-%dT%T'),  tar_msr_no = REPLACE(@tar_msr_no, ' ', ''), adval_rate = nullif(@adval_rate, ''), alch_sgth = nullif(@alch_sgth, ''), audit_tsmp = STR_TO_DATE(@audit_tsmp, '%Y-%m-%dT%T'), cap_ai_stmt = nullif(@cap_ai_stmt, ''), cap_max_pct = nullif(@cap_max_pct, ''), cpc_wvr_phb = nullif(@cpc_wvr_phb, ''), comp_mthd = nullif(@comp_mthd, ''), ec_msr_set = nullif(@ec_msr_set, ''), ec_sctr = nullif(@ec_sctr, ''), mip_band_exch = nullif(@mip_band_exch, ''), mip_rate_exch = nullif(@mip_rate_exch, ''), mip_uoq_code = nullif(@mip_uoq_code, ''), nba_id = nullif(@nba_id, ''), null_tri_rqd = nullif(@null_tri_rqd, ''), qta_code_uk = nullif(@qta_code_uk, ''), qta_elig_use = nullif(@qta_elig_use, ''), qta_exch_rate = nullif(@qta_exch_rate, ''), qta_no = nullif(@qta_no, ''), qta_uoq_code = nullif(@qta_uoq_code, ''), rfa = nullif(@rfa, ''), rfs_code_1 = nullif(@rfs_code_1, ''), rfs_code_2 = nullif(@rfs_code_2, ''), rfs_code_3 = nullif(@rfs_code_3, ''), rfs_code_4 = nullif(@rfs_code_4, ''), rfs_code_5 = nullif(@rfs_code_5, ''), tdr_spr_sur = nullif(@tdr_spr_sur, ''), exports_use_ind = CASE WHEN @exports_use_ind = 'N' THEN 0 ELSE 1 END, amend_indicator = 'I';

LOAD DATA INFILE "MFCM.csv" INTO TABLE chief_mfcm COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@fe_tsmp, msrgp_code, msr_type, @tty_code, @tar_msr_no, @le_tsmp, @dummy, @audit_tsmp, @cmdty_code, @cmdty_msr_xhdg, @null_tri_rqd, @exports_use_ind) SET fe_tsmp = STR_TO_DATE(@fe_tsmp, '%Y-%m-%dT%T'), le_tsmp = STR_TO_DATE(@le_tsmp, '%Y-%m-%dT%T'), audit_tsmp = STR_TO_DATE(@audit_tsmp, '%Y-%m-%dT%T'), tar_msr_no = REPLACE(@tar_msr_no, ' ', ''), cmdty_code = REPLACE(@cmdty_code, ' ', ''),  exports_use_ind = CASE WHEN @exports_use_ind = 'N' THEN 0 ELSE 1 END, null_tri_rqd = REPLACE(@null_tri_rqd, 'N', 0), null_tri_rqd = REPLACE(@null_tri_rqd, 'Y', 1), null_tri_rqd = REPLACE(@null_tri_rqd, ' ', 0), cmdty_msr_xhdg = REPLACE(@cmdty_msr_xhdg, 'N', 0),  cmdty_msr_xhdg = REPLACE(@cmdty_msr_xhdg, 'Y', 1),  cmdty_msr_xhdg = REPLACE(@cmdty_msr_xhdg, ' ', 0),  tty_code = nullif(@tty_code, ''), amend_indicator = 'I';

DELETE FROM chief_mfcm WHERE msrgp_code NOT IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');
DELETE FROM chief_tame WHERE msrgp_code NOT IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');
DELETE FROM chief_tamf WHERE msrgp_code NOT IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');

DELETE FROM chief_mfcm WHERE LENGTH(cmdty_code) = 9;
DELETE FROM chief_mfcm WHERE LENGTH(cmdty_code) = 8 AND msr_type NOT IN ('CEX','CON','ECM','EHC','EQC','EWP','PRE');

UPDATE chief_mfcm SET tar_msr_no = NULL where tar_msr_no = '';
UPDATE chief_tame SET tar_msr_no = NULL where tar_msr_no = '';
UPDATE chief_tamf SET tar_msr_no = NULL where tar_msr_no = '';

UPDATE chief_tame SET le_tsmp = NULL WHERE le_tsmp = '0000-00-00 00:00:00';
UPDATE chief_mfcm SET le_tsmp = NULL WHERE le_tsmp = '0000-00-00 00:00:00';

LOAD DATA INFILE "TBL9.csv" INTO TABLE chief_tbl9 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@fe_tsmp, @tbl_type, @tbl_code, txtlnno, @amend_indicator, @tbl_txt) SET fe_tsmp = STR_TO_DATE(@fe_tsmp, '%Y-%m-%dT%T'), tbl_type = nullif(@tbl_type, ''), tbl_code = nullif(@tbl_code, ''), tbl_txt = nullif(@tbl_txt, ''), amend_indicator = 'I';

LOAD DATA INFILE "COMM.csv" INTO TABLE chief_comm COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (@fe_tsmp, @cmdty_code, @le_tsmp, @curr_ind, @add_rlf_alwd_ind, @alcohol_cmdty, @audit_tsmp, @chi_doti_rqd, @cmdty_bbeer, @cmdty_beer, @cmdty_euse_alwd, @cmdty_exp_rfnd, @cmdty_mdecln, @exp_lcnc_rqd, @ex_ec_scode_rqd, @full_dty_adval1, @full_dty_adval2, @full_dty_exch, @full_dty_spfc1, @full_dty_spfc2, @full_dty_type, @full_dty_uoq_c2, @full_dty_uoq1, @full_dty_uoq2, @full_duty_type, @im_ec_score_rqd, @imp_exp_use, @nba_id, @perfume_cmdty, @rfa, @season_end, @season_start, @spv_code, @spv_xhdg, @uoq_code_cdu1, @uoq_code_cdu2, @uoq_code_cdu3, @whse_cmdty, @wines_cmdty) SET fe_tsmp = STR_TO_DATE(@fe_tsmp, '%Y-%m-%dT%T'), amend_indicator = 'I', cmdty_code = nullif(@cmdty_code, ''), le_tsmp = STR_TO_DATE(@le_tsmp, '%Y-%m-%dT%T'), add_rlf_alwd_ind = CASE WHEN @add_rlf_alwd_ind = 'N' THEN 0 ELSE 1 END, alcohol_cmdty = CASE WHEN @alcohol_cmdty = 'N' THEN 0 ELSE 1 END, audit_tsmp = STR_TO_DATE(@audit_tsmp, '%Y-%m-%dT%T'), chi_doti_rqd = CASE WHEN @chi_doti_rqd = 'N' THEN 0 ELSE 1 END, cmdty_bbeer = CASE WHEN @cmdty_bbeer = 'N' THEN 0 ELSE 1 END, cmdty_beer = CASE WHEN @cmdty_beer = 'N' THEN 0 ELSE 1 END, cmdty_euse_alwd = CASE WHEN @cmdty_euse_alwd = 'N' THEN 0 ELSE 1 END, cmdty_exp_rfnd = CASE WHEN @cmdty_exp_rfnd = 'N' THEN 0 ELSE 1 END, cmdty_mdecln = CASE WHEN @cmdty_mdecln = 'N' THEN 0 ELSE 1 END, exp_lcnc_rqd = CASE WHEN @exp_lcnc_rqd = 'N' THEN 0 ELSE 1 END, ex_ec_scode_rqd = CASE WHEN @ex_ec_scode_rqd = 'N' THEN 0 ELSE 1 END, full_dty_adval1 = nullif(@full_dty_adval1, ''), full_dty_adval2 = nullif(@full_dty_adval2, ''), full_dty_exch = nullif(@full_dty_exch, ''), full_dty_spfc1 = nullif(@full_dty_spfc1, ''), full_dty_spfc2 = nullif(@full_dty_spfc2, ''), full_duty_type = nullif(@full_duty_type, ''), full_dty_uoq_c2 = nullif(@full_duty_uoq_c2, ''), full_dty_uoq1 = nullif(@full_dty_uoq1, ''), full_dty_uoq2 = nullif(@full_dty_uoq2, ''), full_duty_type = nullif(@full_duty_type, ''), imp_exp_use = CASE WHEN @imp_exp_use = 'N' THEN 0 ELSE 1 END, nba_id = nullif(@nba_id, ''), perfume_cmdty = CASE WHEN @perfume_cmdty = 'N' THEN 0 ELSE 1 END, rfa = nullif(@rfa, ''), season_end = nullif(@season_end, ''), season_start = nullif(@season_start, ''), spv_code = nullif(@spv_code, ''), spv_xhdg = CASE WHEN @spv_xhdg = 'N' THEN 0 ELSE 1 END, uoq_code_cdu1 = nullif(@uoq_code_cdu1, ''), uoq_code_cdu2 = nullif(@uoq_code_cdu2, ''), uoq_code_cdu3 = nullif(@uoq_code_cdu3, ''), whse_cmdty = CASE WHEN @whse_cmdty = 'N' THEN 0 ELSE 1 END, wines_cmdty = CASE WHEN @wines_cmdty = 'N' THEN 0 ELSE 1 END;

UPDATE chief_comm SET cmdty_code = REPLACE(cmdty_code, ' ', '');
DELETE FROM chief_comm WHERE LENGTH(cmdty_code) < 10;