This repository has been archived by the owner on Dec 30, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 2
Importing CHIEF initial seed from CSV to Postgresql
Hildebrando Rueda edited this page Jul 7, 2016
·
5 revisions
---------------- LOAD chief_tamf table ----------------
-- Create temporary table to store CVS contents
CREATE TEMPORARY TABLE temp_chief_tamf
(
fe_tsmp timestamp without time zone,
msrgp_code varchar(255),
msr_type varchar(255),
tty_code varchar(255),
tar_msr_no varchar(255),
current_ind varchar(255),
adval1_rate varchar(255),
adval2_rate varchar(255),
ai_factor varchar(255),
cmdty_dmql varchar(255),
cmdty_dmql_uoq varchar(255),
cngp_code varchar(255),
cntry_disp varchar(255),
cntry_orig varchar(255),
duty_type varchar(255),
ec_supplement varchar(255),
ec_exch_rate varchar(255),
spcl_inst varchar(255),
spfc1_cmpd_uoq varchar(255),
spfc1_rate varchar(255),
spfc1_uoq varchar(255),
spfc2_rate varchar(255),
spfc2_uoq varchar(255),
spfc3_rate varchar(255),
spfc3_uoq varchar(255),
tamf_dt varchar(255),
tamf_sta varchar(255),
tamf_ty varchar(255)
);
-- Move contents from CVS to temporary table
COPY temp_chief_tamf FROM '/CHIEF_INITIAL_CSV/TAMF.csv' WITH CSV HEADER;
-- Insert into desired table from temporary table
INSERT INTO chief_tamf (fe_tsmp, msrgp_code, msr_type, tty_code, tar_msr_no, 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, amend_indicator)
SELECT fe_tsmp,
NULLIF(msrgp_code, '') AS msrgp_code,
NULLIF(msr_type, '') AS msr_type,
NULLIF(tty_code, '') AS tty_code,
NULLIF(REPLACE(tar_msr_no, ' ', ''), '') AS tar_msr_no,
NULLIF(adval1_rate, '')::numeric AS adval1_rate,
NULLIF(adval2_rate, '')::numeric AS adval2_rate,
NULLIF(ai_factor, '') as ai_factor,
NULLIF(cmdty_dmql, '')::numeric AS cmdty_dmql,
NULLIF(cmdty_dmql_uoq, '') as cmdty_dmql_uoq,
NULLIF(cngp_code, '') as cngp_code,
NULLIF(cntry_disp, '') as cntry_disp,
NULLIF(cntry_orig, '') as cntry_orig,
NULLIF(duty_type, '') as duty_type,
NULLIF(ec_supplement, '') as ec_supplement,
NULLIF(ec_exch_rate, '') as ec_exch_rate,
NULLIF(spcl_inst, '') as spcl_inst,
NULLIF(spfc1_cmpd_uoq, '') as spfc1_cmpd_uoq,
NULLIF(spfc1_rate, '')::numeric AS spfc1_rate,
NULLIF(spfc1_uoq, '') as spfc1_uoq,
NULLIF(spfc2_rate, '')::numeric AS spfc2_rate,
NULLIF(spfc2_uoq, '') as spfc2_uoq,
NULLIF(spfc3_rate, '')::numeric AS spfc3_rate,
NULLIF(spfc3_uoq, '') as spfc3_uoq,
NULLIF(tamf_dt, '') as tamf_dt,
NULLIF(tamf_sta, '') as tamf_sta,
NULLIF(tamf_ty, '') as tamf_ty,
'I' AS amend_indicator
FROM temp_chief_tamf WHERE msrgp_code IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');
-- Drop temporary table
DROP TABLE temp_chief_tamf;
---------------- LOAD chief_tame table ----------------
-- Create temporary table to store CVS contents
CREATE TEMPORARY TABLE temp_chief_tame
(
fe_tsmp timestamp without time zone,
msrgp_code varchar(255),
msr_type varchar(255),
tty_code varchar(255),
tar_msr_no varchar(255),
le_tsmp varchar(255),
current_ind varchar(255),
adval_rate varchar(255),
alch_sgth varchar(255),
audit_tsmp varchar(255),
cap_ai_stmt varchar(255),
cap_max_pct varchar(255),
cmdty_msr_xhdg varchar(255),
comp_mthd varchar(255),
cpc_wvr_phb varchar(255),
ec_msr_set varchar(255),
ec_sctr varchar(255),
mip_band_exch varchar(255),
mip_rate_exch varchar(255),
mip_uoq_code varchar(255),
nba_id varchar(255),
null_tri_rqd varchar(255),
qta_code_uk varchar(255),
qta_elig_use varchar(255),
qta_exch_rate varchar(255),
qta_no varchar(255),
qta_uoq_code varchar(255),
rfa varchar(255),
rfs_code_1 varchar(255),
rfs_code_2 varchar(255),
rfs_code_3 varchar(255),
rfs_code_4 varchar(255),
rfs_code_5 varchar(255),
tdr_spr_sur varchar(255),
exports_use_ind varchar(255)
);
-- Move contents from CVS to temporary table
COPY temp_chief_tame FROM '/CHIEF_INITIAL_CSV/TAME.csv' WITH CSV HEADER;
-- Insert into desired table from temporary table
INSERT INTO chief_tame (fe_tsmp, msrgp_code, msr_type, tty_code, tar_msr_no, le_tsmp, 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, amend_indicator)
SELECT fe_tsmp,
NULLIF(msrgp_code, '') AS msrgp_code,
NULLIF(msr_type, '') AS msr_type,
NULLIF(tty_code, '') AS tty_code,
NULLIF(REPLACE(tar_msr_no, ' ', ''), '') AS tar_msr_no,
NULLIF(le_tsmp, '')::timestamp without time zone AS le_tsmp,
NULLIF(adval_rate, '')::numeric AS adval_rate,
NULLIF(alch_sgth, '')::numeric AS alch_sgth,
NULLIF(audit_tsmp, '')::timestamp without time zone AS audit_tsmp,
NULLIF(cap_ai_stmt, '') AS cap_ai_stmt,
NULLIF(cap_max_pct, '')::numeric AS cap_max_pct,
NULLIF(cmdty_msr_xhdg, '') AS cmdty_msr_xhdg,
NULLIF(comp_mthd, '') AS comp_mthd,
NULLIF(cpc_wvr_phb, '') AS cpc_wvr_phb,
NULLIF(ec_msr_set, '') AS ec_msr_set,
NULLIF(ec_sctr, '') AS ec_sctr,
NULLIF(mip_band_exch, '') AS mip_band_exch,
NULLIF(mip_rate_exch, '') AS mip_rate_exch,
NULLIF(mip_uoq_code, '') AS mip_uoq_code,
NULLIF(nba_id, '') AS nba_id,
NULLIF(null_tri_rqd, '') AS null_tri_rqd,
NULLIF(qta_code_uk, '') AS qta_code_uk,
NULLIF(qta_elig_use, '') AS qta_elig_use,
NULLIF(qta_exch_rate, '') AS qta_exch_rate,
NULLIF(qta_no, '') AS qta_no,
NULLIF(qta_uoq_code, '') AS qta_uoq_code,
NULLIF(rfa, '') AS rfa,
NULLIF(rfs_code_1, '') AS rfs_code_1,
NULLIF(rfs_code_2, '') AS rfs_code_2,
NULLIF(rfs_code_3, '') AS rfs_code_3,
NULLIF(rfs_code_4, '') AS rfs_code_4,
NULLIF(rfs_code_5, '') AS rfs_code_5,
NULLIF(tdr_spr_sur, '') AS tdr_spr_sur,
NULLIF(exports_use_ind, '')::boolean AS exports_use_ind,
'I' AS amend_indicator
FROM temp_chief_tame WHERE msrgp_code IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');
-- Drop temporary table
DROP TABLE temp_chief_tame;
---------------- LOAD chief_mfcm table ----------------
-- Create temporary table to store CVS contents
CREATE TEMPORARY TABLE temp_chief_mfcm
(
fe_tsmp timestamp without time zone,
msrgp_code varchar(255),
msr_type varchar(255),
tty_code varchar(255),
tar_msr_no varchar(255),
le_tsmp varchar(255),
current_ind varchar(255),
audit_tsmp varchar(255),
cmdty_code varchar(255),
cmdty_msr_xhdg varchar(255),
null_tri_rqd varchar(255),
exports_use_ind varchar(255)
);
-- Move contents from CVS to temporary table
COPY temp_chief_mfcm FROM '/CHIEF_INITIAL_CSV/MFCM.csv' WITH CSV HEADER;
-- Insert into desired table from temporary table
INSERT INTO chief_mfcm (fe_tsmp, msrgp_code, msr_type, tty_code, tar_msr_no, le_tsmp, audit_tsmp, cmdty_code, cmdty_msr_xhdg, null_tri_rqd, exports_use_ind, amend_indicator)
SELECT fe_tsmp,
NULLIF(msrgp_code, '') AS msrgp_code,
NULLIF(msr_type, '') AS msr_type,
NULLIF(tty_code, '') AS tty_code,
NULLIF(REPLACE(tar_msr_no, ' ', ''), '') AS tar_msr_no,
NULLIF(le_tsmp, '')::timestamp without time zone AS le_tsmp,
NULLIF(audit_tsmp, '')::timestamp without time zone AS audit_tsmp,
NULLIF(REPLACE(cmdty_code, ' ', ''), '') AS cmdty_code,
NULLIF(cmdty_msr_xhdg, '') AS cmdty_msr_xhdg,
NULLIF(null_tri_rqd, '') AS null_tri_rqd,
NULLIF(exports_use_ind, '')::boolean AS exports_use_ind,
'I' AS amend_indicator
FROM temp_chief_mfcm WHERE msrgp_code IN ('EX', 'VT', 'DL', 'PR', 'DP', 'DO', 'HO');
-- Delete unwanted records
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');
-- Drop temporary table
DROP TABLE temp_chief_mfcm;
---------------- LOAD chief_tbl9 table ----------------
-- Create temporary table to store CVS contents
CREATE TEMPORARY TABLE temp_chief_tbl9
(
fe_tsmp timestamp without time zone,
tbl_type varchar(255),
tbl_code varchar(255),
txtlnno varchar(255),
current_ind varchar(255),
tbl_txt varchar(255)
);
-- Move contents from CVS to temporary table
COPY temp_chief_tbl9 FROM '/CHIEF_INITIAL_CSV/TBL9.csv' WITH CSV HEADER;
-- Insert into desired table from temporary table
INSERT INTO chief_tbl9 (fe_tsmp, amend_indicator, tbl_type, tbl_code, txtlnno, tbl_txt)
SELECT fe_tsmp,
'I' AS amend_indicator,
NULLIF(tbl_type, '') AS tbl_type,
NULLIF(tbl_code, '') AS tbl_code,
NULLIF(txtlnno, '')::numeric AS txtlnno,
NULLIF(tbl_txt, '') AS tbl_txt
FROM temp_chief_tbl9;
-- Drop temporary table
DROP TABLE temp_chief_tbl9;
---------------- LOAD chief_comm table ----------------
-- Create temporary table to store CVS contents
CREATE TEMPORARY TABLE temp_chief_comm
(
fe_tsmp timestamp without time zone,
cmdty_code varchar(255),
le_tsmp varchar(255),
current_ind varchar(255),
add_rlf_alwd_ind varchar(255),
alcohol_cmdty varchar(255),
audit_tsmp varchar(255),
chi_doti_rqd varchar(255),
cmdty_bbeer varchar(255),
cmdty_beer varchar(255),
cmdty_euse_alwd varchar(255),
cmdty_exp_rfnd varchar(255),
cmdty_mdecln varchar(255),
exp_lcnc_rqd varchar(255),
ex_ec_scode_rqd varchar(255),
full_dty_adval1 varchar(255),
full_dty_adval2 varchar(255),
full_dty_exch varchar(255),
full_dty_spfc1 varchar(255),
full_dty_spfc2 varchar(255),
full_dty_type varchar(255),
full_dty_uoq_c2 varchar(255),
full_dty_uoq1 varchar(255),
full_dty_uoq2 varchar(255),
full_duty_type varchar(255),
im_ec_score_rqd varchar(255),
imp_exp_use varchar(255),
nba_id varchar(255),
perfume_cmdty varchar(255),
rfa varchar(255),
season_end varchar(255),
season_start varchar(255),
spv_code varchar(255),
spv_xhdg varchar(255),
uoq_code_cdu1 varchar(255),
uoq_code_cdu2 varchar(255),
uoq_code_cdu3 varchar(255),
whse_cmdty varchar(255),
wines_cmdty varchar(255)
);
-- Move contents from CVS to temporary table
COPY temp_chief_comm FROM '/CHIEF_INITIAL_CSV/COMM.csv' WITH CSV HEADER;
-- Insert into desired table from temporary table
INSERT INTO chief_comm (fe_tsmp, amend_indicator, cmdty_code, le_tsmp, 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_ttype, 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)
SELECT fe_tsmp,
'I' AS amend_indicator,
NULLIF(REPLACE(cmdty_code, ' ', ''), '') AS cmdty_code,
NULLIF(le_tsmp, '')::timestamp without time zone AS le_tsmp,
CASE WHEN add_rlf_alwd_ind = 'N' THEN false ELSE true END AS add_rlf_alwd_ind,
CASE WHEN alcohol_cmdty = 'N' THEN false ELSE true END AS alcohol_cmdty,
NULLIF(audit_tsmp, '')::timestamp without time zone AS audit_tsmp,
CASE WHEN chi_doti_rqd = 'N' THEN false ELSE true END AS chi_doti_rqd,
CASE WHEN cmdty_bbeer = 'N' THEN false ELSE true END AS cmdty_bbeer,
CASE WHEN cmdty_beer = 'N' THEN false ELSE true END AS cmdty_beer,
CASE WHEN cmdty_euse_alwd = 'N' THEN false ELSE true END AS cmdty_euse_alwd,
CASE WHEN cmdty_exp_rfnd = 'N' THEN false ELSE true END AS cmdty_exp_rfnd,
CASE WHEN cmdty_mdecln = 'N' THEN false ELSE true END AS cmdty_mdecln,
CASE WHEN exp_lcnc_rqd = 'N' THEN false ELSE true END AS exp_lcnc_rqd,
CASE WHEN ex_ec_scode_rqd = 'N' THEN false ELSE true END AS ex_ec_scode_rqd,
NULLIF(full_dty_adval1, '')::numeric AS full_dty_adval1,
NULLIF(full_dty_adval2, '')::numeric AS full_dty_adval2,
NULLIF(full_dty_exch, '') AS full_dty_exch,
NULLIF(full_dty_spfc1, '')::numeric AS full_dty_spfc1,
NULLIF(full_dty_spfc2, '')::numeric AS full_dty_spfc2,
NULLIF(full_dty_type, '') AS full_dty_ttype,
NULLIF(full_dty_uoq_c2, '') AS full_dty_uoq_c2,
NULLIF(full_dty_uoq1, '') AS full_dty_uoq1,
NULLIF(full_dty_uoq2, '') AS full_dty_uoq2,
NULLIF(full_duty_type, '') AS full_duty_type,
CASE WHEN im_ec_score_rqd = 'N' THEN false ELSE true END AS im_ec_score_rqd,
CASE WHEN imp_exp_use = 'N' THEN false ELSE true END AS imp_exp_use,
NULLIF(nba_id, '') AS nba_id,
CASE WHEN perfume_cmdty = 'N' THEN false ELSE true END AS perfume_cmdty,
NULLIF(rfa, '') AS rfa,
NULLIF(season_end, '')::numeric AS season_end,
NULLIF(season_start, '')::numeric AS season_start,
NULLIF(spv_code, '') AS spv_code,
CASE WHEN spv_xhdg = 'N' THEN false ELSE true END AS spv_xhdg,
NULLIF(uoq_code_cdu1, '') AS uoq_code_cdu1,
NULLIF(uoq_code_cdu2, '') AS uoq_code_cdu2,
NULLIF(uoq_code_cdu3, '') AS uoq_code_cdu3,
CASE WHEN whse_cmdty = 'N' THEN false ELSE true END AS whse_cmdty,
CASE WHEN wines_cmdty = 'N' THEN false ELSE true END AS wines_cmdty
FROM temp_chief_comm;
-- Delete unwanted records
DELETE FROM chief_comm WHERE LENGTH(cmdty_code) < 10;
-- Drop temporary table
DROP TABLE temp_chief_comm;