search

rkiddy / open_calaccess_data_perl

Information about the data made available by the California Secretary of State about campaign finances and lobbying in the state. Not the data itself, just info about it.
0 stars 0 forks source link

Issues with import: 20131212 - 20131213 #4

Closed rkiddy closed 10 years ago

rkiddy commented 10 years ago

replace into expn (pk, agent_namf, agent_naml, agent_nams, agent_namt, amend_id, amount, bakref_tid, bal_juris, bal_name, bal_num, cand_namf, cand_naml, cand_nams, cand_namt, cmte_id, cum_oth, cum_ytd, dist_no, entity_cd, expn_chkno, expn_code, expn_date, expn_dscr, filing_id, form_type, g_from_e_f, juris_cd, juris_dscr, line_item, memo_code, memo_refno, off_s_h_cd, offic_dscr, office_cd, payee_city, payee_namf, payee_naml, payee_nams, payee_namt, payee_st, payee_zip4, rec_type, sup_opp_cd, tran_id, tres_city, tres_namf, tres_naml, tres_nams, tres_namt, tres_st, tres_zip4, xref_match, xref_schnm) select pk, agent_namf, agent_naml, agent_nams, agent_namt, amend_id, amount, bakref_tid, bal_juris, bal_name, bal_num, cand_namf, cand_naml, cand_nams, cand_namt, cmte_id, cum_oth, cum_ytd, dist_no, entity_cd, expn_chkno, expn_code, expn_date, expn_dscr, filing_id, form_type, g_from_e_f, juris_cd, juris_dscr, line_item, memo_code, memo_refno, off_s_h_cd, offic_dscr, office_cd, payee_city, payee_namf, payee_naml, payee_nams, payee_namt, payee_st, payee_zip4, rec_type, sup_opp_cd, tran_id, tres_city, tres_namf, tres_naml, tres_nams, tres_namt, tres_st, tres_zip4, xref_match, xref_schnm from expn_cd where pk > 4082322

Query OK, 48 rows affected, 40 warnings (0.18 sec) Records: 48 Duplicates: 0 Warnings: 0

show warnings

+---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 1 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 2 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 3 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_ytd' at row 3 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 4 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_ytd' at row 4 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 5 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_ytd' at row 5 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 6 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_ytd' at row 6 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 19 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 20 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 21 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 22 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 23 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 24 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 25 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 26 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 27 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 28 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 29 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 30 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 31 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 32 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 33 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 34 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 35 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 36 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 37 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 38 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 39 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 40 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 41 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 42 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 43 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 44 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 45 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 46 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 47 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 48 | +---------+------+------------------------------------------------------------+ 40 rows in set (0.00 sec)

replace into filer_filings (pk, filer_id, filing_id, period_id, form_id, filing_sequence, filing_date, stmnt_type, stmnt_status, session_id, user_id, special_audit, fine_audit, rpt_start, rpt_end, rpt_date, filing_type) select pk, filer_id, filing_id, period_id, form_id, filing_sequence, filing_date, stmnt_type, stmnt_status, session_id, user_id, special_audit, fine_audit, rpt_start, rpt_end, rpt_date, filing_type from filer_filings_cd where pk > 1645984

Query OK, 80 rows affected, 60 warnings (0.43 sec) Records: 80 Duplicates: 0 Warnings: 0

show warnings

+---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 2 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 3 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 4 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 6 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 9 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 10 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 11 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 12 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 13 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 16 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 17 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 18 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 19 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 20 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 21 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 22 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 23 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 24 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 25 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 26 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 27 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 28 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 30 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 32 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 33 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 35 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 36 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 37 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 39 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 40 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 42 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 43 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 44 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 47 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 49 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 51 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 52 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 53 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 54 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 55 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 56 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 58 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 60 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 61 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 64 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 65 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 66 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 67 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 68 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 69 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 70 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 71 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 72 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 73 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 74 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 76 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 77 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 78 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 79 | | Warning | 1366 | Incorrect integer value: '' for column 'period_id' at row 80 | +---------+------+--------------------------------------------------------------+ 60 rows in set (0.00 sec)

replace into rcpt (pk, amend_id, amount, bakref_tid, bal_juris, bal_name, bal_num, cand_namf, cand_naml, cand_nams, cand_namt, cmte_id, ctrib_city, ctrib_dscr, ctrib_emp, ctrib_namf, ctrib_naml, ctrib_nams, ctrib_namt, ctrib_occ, ctrib_self, ctrib_st, ctrib_zip4, cum_oth, cum_ytd, date_thru, dist_no, entity_cd, filing_id, form_type, int_rate, intr_city, intr_cmteid, intr_emp, intr_namf, intr_naml, intr_nams, intr_namt, intr_occ, intr_self, intr_st, intr_zip4, juris_cd, juris_dscr, line_item, memo_code, memo_refno, off_s_h_cd, offic_dscr, office_cd, rcpt_date, rec_type, sup_opp_cd, tran_id, tran_type, tres_city, tres_namf, tres_naml, tres_nams, tres_namt, tres_st, tres_zip4, xref_match, xref_schnm) select pk, amend_id, amount, bakref_tid, bal_juris, bal_name, bal_num, cand_namf, cand_naml, cand_nams, cand_namt, cmte_id, ctrib_city, ctrib_dscr, ctrib_emp, ctrib_namf, ctrib_naml, ctrib_nams, ctrib_namt, ctrib_occ, ctrib_self, ctrib_st, ctrib_zip4, cum_oth, cum_ytd, date_thru, dist_no, entity_cd, filing_id, form_type, int_rate, intr_city, intr_cmteid, intr_emp, intr_namf, intr_naml, intr_nams, intr_namt, intr_occ, intr_self, intr_st, intr_zip4, juris_cd, juris_dscr, line_item, memo_code, memo_refno, off_s_h_cd, offic_dscr, office_cd, rcpt_date, rec_type, sup_opp_cd, tran_id, tran_type, tres_city, tres_namf, tres_naml, tres_nams, tres_namt, tres_st, tres_zip4, xref_match, xref_schnm from rcpt_cd where pk > 7977804

Query OK, 17 rows affected, 17 warnings (0.48 sec) Records: 17 Duplicates: 0 Warnings: 0

show warnings

+---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 1 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 2 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 3 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 4 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 5 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 6 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 7 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 8 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 9 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 10 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 11 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 12 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 13 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 14 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 15 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 16 | | Warning | 1366 | Incorrect decimal value: '' for column 'cum_oth' at row 17 | +---------+------+------------------------------------------------------------+ 17 rows in set (0.00 sec)

replace into smry (pk, filing_id, amend_id, line_item, rec_type, form_type, amount_a, amount_b, amount_c, elec_dt) select pk, filing_id, amend_id, line_item, rec_type, form_type, amount_a, amount_b, amount_c, elec_dt from smry_cd where pk > 6602096

Query OK, 72 rows affected, 135 warnings (0.03 sec) Records: 72 Duplicates: 0 Warnings: 0

show warnings

+---------+------+-------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------+ | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 1 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 1 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 2 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 2 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 3 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 3 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 4 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 4 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 5 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 5 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 6 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 6 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 7 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 8 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 8 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 9 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 9 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 10 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 11 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 12 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 12 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 13 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 13 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 14 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 14 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 15 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 15 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 16 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 16 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 17 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 17 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 18 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 18 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 19 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 19 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 20 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 20 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 21 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 21 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 22 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 22 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 23 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 23 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 24 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 24 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 25 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 25 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 26 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 27 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 27 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 28 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 28 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_a' at row 29 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 29 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 29 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_a' at row 30 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 30 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 30 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 31 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 31 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 32 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 32 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 33 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 33 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 34 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 34 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 35 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 35 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 36 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 36 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 37 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 38 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 38 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 39 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 39 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 40 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 40 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 41 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 42 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 42 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 43 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 44 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 45 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 46 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 47 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 48 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 48 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 49 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 49 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 50 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 50 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 51 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 51 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 52 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 52 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 53 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 53 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 54 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 54 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 55 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 55 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 56 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 56 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 57 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 57 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 58 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 58 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 59 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 59 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 60 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 60 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 61 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 61 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 62 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 62 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 63 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 63 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 64 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 64 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 65 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 65 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 66 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 66 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 67 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 67 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 68 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 68 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 69 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 69 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 70 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 70 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 71 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 71 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_b' at row 72 | | Warning | 1366 | Incorrect decimal value: '' for column 'amount_c' at row 72 | +---------+------+-------------------------------------------------------------+ 135 rows in set (0.00 sec)

rkiddy commented 10 years ago

Others have this same issue for the same columns.

rkiddy commented 10 years ago

Fixed most of the rows with obvious changes. For example, if there are a series of dates 2002, 2002, 8002, 2002, it seems pretty obvious that it can be fixed.

mysql> select count(_) from filer_filings where filingdate < '1974'; +----------+ | count() | +----------+ | 53 | +----------+

mysql> select substr(filingdate,1,7) as month, count() from filerfilings group by month order by month; +---------+----------+ | month | count() | +---------+----------+ | NULL | 891 | | 1900-01 | 3 | | 1900-02 | 5 | | 1900-03 | 3 | | 1900-04 | 1 | | 1900-05 | 1 | | 1900-06 | 1 | | 1900-07 | 2 | | 1900-09 | 1 | | 1909-10 | 1 | | 1910-10 | 6 | | 1911-01 | 2 | | 1917-02 | 1 | | 1919-05 | 1 | | 1923-10 | 3 | | 1924-03 | 1 | | 1934-07 | 1 | | 1937-08 | 1 | | 1940-07 | 3 | | 1944-10 | 6 | | 1944-11 | 1 | | 1948-10 | 1 | | 1960-04 | 1 | | 1961-01 | 1 | | 1966-01 | 1 | | 1967-09 | 1 | | 1969-02 | 1 | | 1969-10 | 1 | | 1971-08 | 1 | | 1973-02 | 1 | | 1974-01 | 20 | | 1974-02 | 481 | | 1974-03 | 105 | | 1974-04 | 13 | | 1974-05 | 51 | | 1974-06 | 9 | | 1974-07 | 19 | | 1974-08 | 10 | | 1974-09 | 7 | | 1974-10 | 36 | | 1974-11 | 12 | | 1974-12 | 10 | | 1975-01 | 156 | | 1975-02 | 287 | | 1975-03 | 29 | | 1975-04 | 14 | | 1975-05 | 13 | | 1975-06 | 27 | | 1975-07 | 44 | | 1975-08 | 42 | | 1975-09 | 35 | | 1975-10 | 39 | | 1975-11 | 28 | | 1975-12 | 42 | | 1976-01 | 305 | | 1976-02 | 337 | | 1976-03 | 78 | | 1976-04 | 77 | | 1976-05 | 49 | | 1976-06 | 17 | | 1976-07 | 28 | | 1976-08 | 23 | | 1976-09 | 29 | | 1976-10 | 42 | | 1976-11 | 23 | | 1976-12 | 10 | | 1977-01 | 37 | | 1977-02 | 30 | | 1977-03 | 36 | | 1977-04 | 16 | | 1977-05 | 28 | | 1977-06 | 16 | | 1977-07 | 51 | | 1977-08 | 53 | | 1977-09 | 36 | | 1977-10 | 57 | | 1977-11 | 51 | | 1977-12 | 56 | | 1978-01 | 184 | | 1978-02 | 133 | | 1978-03 | 359 | | 1978-04 | 1059 | | 1978-05 | 4172 | | 1978-06 | 992 | | 1978-07 | 2369 | | 1978-08 | 839 | | 1978-09 | 976 | | 1978-10 | 3878 | | 1978-11 | 1164 | | 1978-12 | 532 | | 1979-01 | 2498 | | 1979-02 | 830 | | 1979-03 | 662 | | 1979-04 | 623 | | 1979-05 | 499 | | 1979-06 | 537 | | 1979-07 | 1693 | | 1979-08 | 607 | | 1979-09 | 539 | | 1979-10 | 799 | | 1979-11 | 415 | | 1979-12 | 324 | | 1980-01 | 2118 | | 1980-02 | 1156 | | 1980-03 | 1146 | | 1980-04 | 1927 | | 1980-05 | 3000 | | 1980-06 | 1264 | | 1980-07 | 2726 | | 1980-08 | 1287 | | 1980-09 | 2757 | | 1980-10 | 3410 | | 1980-11 | 984 | | 1980-12 | 505 | | 1981-01 | 1747 | | 1981-02 | 1477 | | 1981-03 | 928 | | 1981-04 | 464 | | 1981-05 | 982 | | 1981-06 | 343 | | 1981-07 | 1946 | | 1981-08 | 649 | | 1981-09 | 1196 | | 1981-10 | 1214 | | 1981-11 | 495 | | 1981-12 | 359 | | 1982-01 | 1724 | | 1982-02 | 1865 | | 1982-03 | 3010 | | 1982-04 | 1199 | | 1982-05 | 2566 | | 1982-06 | 1552 | | 1982-07 | 2192 | | 1982-08 | 1996 | | 1982-09 | 2616 | | 1982-10 | 3736 | | 1982-11 | 1014 | | 1982-12 | 490 | | 1983-01 | 3174 | | 1983-02 | 1061 | | 1983-03 | 1273 | | 1983-04 | 631 | | 1983-05 | 1375 | | 1983-06 | 490 | | 1983-07 | 1673 | | 1983-08 | 1265 | | 1983-09 | 1806 | | 1983-10 | 1505 | | 1983-11 | 757 | | 1983-12 | 533 | | 1984-01 | 2762 | | 1984-02 | 1218 | | 1984-03 | 2300 | | 1984-04 | 781 | | 1984-05 | 2509 | | 1984-06 | 850 | | 1984-07 | 2821 | | 1984-08 | 1044 | | 1984-09 | 2524 | | 1984-10 | 3134 | | 1984-11 | 1250 | | 1984-12 | 500 | | 1985-01 | 2848 | | 1985-02 | 994 | | 1985-03 | 1183 | | 1985-04 | 571 | | 1985-05 | 1476 | | 1985-06 | 492 | | 1985-07 | 2109 | | 1985-08 | 1013 | | 1985-09 | 1712 | | 1985-10 | 1592 | | 1985-11 | 412 | | 1985-12 | 594 | | 1986-01 | 2932 | | 1986-02 | 1450 | | 1986-03 | 2573 | | 1986-04 | 1092 | | 1986-05 | 2665 | | 1986-06 | 902 | | 1986-07 | 3325 | | 1986-08 | 1310 | | 1986-09 | 1049 | | 1986-10 | 4959 | | 1986-11 | 1013 | | 1986-12 | 494 | | 1987-01 | 2374 | | 1987-02 | 2595 | | 1987-03 | 996 | | 1987-04 | 783 | | 1987-05 | 629 | | 1987-06 | 498 | | 1987-07 | 2618 | | 1987-08 | 1045 | | 1987-09 | 914 | | 1987-10 | 1035 | | 1987-11 | 586 | | 1987-12 | 436 | | 1988-01 | 2019 | | 1988-02 | 2316 | | 1988-03 | 2113 | | 1988-04 | 774 | | 1988-05 | 2370 | | 1988-06 | 1124 | | 1988-07 | 2239 | | 1988-08 | 2402 | | 1988-09 | 997 | | 1988-10 | 3759 | | 1988-11 | 1366 | | 1988-12 | 532 | | 1989-01 | 3921 | | 1989-02 | 1626 | | 1989-03 | 793 | | 1989-04 | 623 | | 1989-05 | 640 | | 1989-06 | 657 | | 1989-07 | 3023 | | 1989-08 | 1519 | | 1989-09 | 810 | | 1989-10 | 927 | | 1989-11 | 681 | | 1989-12 | 554 | | 1990-01 | 3502 | | 1990-02 | 1513 | | 1990-03 | 2944 | | 1990-04 | 1130 | | 1990-05 | 3376 | | 1990-06 | 1306 | | 1990-07 | 4055 | | 1990-08 | 2034 | | 1990-09 | 1137 | | 1990-10 | 5880 | | 1990-11 | 1793 | | 1990-12 | 1637 | | 1991-01 | 4572 | | 1991-02 | 1886 | | 1991-03 | 1111 | | 1991-04 | 754 | | 1991-05 | 1240 | | 1991-06 | 1705 | | 1991-07 | 6102 | | 1991-08 | 1656 | | 1991-09 | 1323 | | 1991-10 | 1150 | | 1991-11 | 763 | | 1991-12 | 704 | | 1992-01 | 4092 | | 1992-02 | 1578 | | 1992-03 | 2748 | | 1992-04 | 1814 | | 1992-05 | 3448 | | 1992-06 | 1015 | | 1992-07 | 4115 | | 1992-08 | 1854 | | 1992-09 | 1435 | | 1992-10 | 5973 | | 1992-11 | 1015 | | 1992-12 | 832 | | 1993-01 | 2742 | | 1993-02 | 3295 | | 1993-03 | 945 | | 1993-04 | 1054 | | 1993-05 | 583 | | 1993-06 | 542 | | 1993-07 | 1813 | | 1993-08 | 2208 | | 1993-09 | 1032 | | 1993-10 | 1240 | | 1993-11 | 620 | | 1993-12 | 602 | | 1994-01 | 3238 | | 1994-02 | 1443 | | 1994-03 | 6179 | | 1994-04 | 3716 | | 1994-05 | 2600 | | 1994-06 | 1436 | | 1994-07 | 2221 | | 1994-08 | 3137 | | 1994-09 | 1165 | | 1994-10 | 4541 | | 1994-11 | 1828 | | 1994-12 | 514 | | 1995-01 | 4008 | | 1995-02 | 1483 | | 1995-03 | 795 | | 1995-04 | 2310 | | 1995-05 | 2622 | | 1995-06 | 592 | | 1995-07 | 6513 | | 1995-08 | 1556 | | 1995-09 | 916 | | 1995-10 | 5029 | | 1995-11 | 947 | | 1995-12 | 819 | | 1996-01 | 7617 | | 1996-02 | 2498 | | 1996-03 | 3515 | | 1996-04 | 4108 | | 1996-05 | 862 | | 1996-06 | 640 | | 1996-07 | 7508 | | 1996-08 | 1889 | | 1996-09 | 1259 | | 1996-10 | 9536 | | 1996-11 | 1764 | | 1996-12 | 1218 | | 1997-01 | 8895 | | 1997-02 | 1889 | | 1997-03 | 910 | | 1997-04 | 4024 | | 1997-05 | 858 | | 1997-06 | 782 | | 1997-07 | 6648 | | 1997-08 | 1500 | | 1997-09 | 1082 | | 1997-10 | 4724 | | 1997-11 | 865 | | 1997-12 | 1341 | | 1998-01 | 4984 | | 1998-02 | 5001 | | 1998-03 | 3374 | | 1998-04 | 4614 | | 1998-05 | 4202 | | 1998-06 | 1277 | | 1998-07 | 8810 | | 1998-08 | 3685 | | 1998-09 | 2192 | | 1998-10 | 9805 | | 1998-11 | 2748 | | 1998-12 | 1314 | | 1999-01 | 4981 | | 1999-02 | 5852 | | 1999-03 | 1038 | | 1999-04 | 4384 | | 1999-05 | 777 | | 1999-06 | 948 | | 1999-07 | 5258 | | 1999-08 | 4528 | | 1999-09 | 1118 | | 1999-10 | 4533 | | 1999-11 | 2590 | | 1999-12 | 1459 | | 2000-01 | 9796 | | 2000-02 | 7364 | | 2000-03 | 6614 | | 2000-04 | 2854 | | 2000-05 | 3411 | | 2000-06 | 1189 | | 2000-07 | 9971 | | 2000-08 | 4366 | | 2000-09 | 1279 | | 2000-10 | 14938 | | 2000-11 | 4572 | | 2000-12 | 3559 | | 2001-01 | 14368 | | 2001-02 | 2982 | | 2001-03 | 1552 | | 2001-04 | 8682 | | 2001-05 | 2291 | | 2001-06 | 1215 | | 2001-07 | 12129 | | 2001-08 | 2284 | | 2001-09 | 1463 | | 2001-10 | 11081 | | 2001-11 | 2141 | | 2001-12 | 3143 | | 2002-01 | 17152 | | 2002-02 | 10073 | | 2002-03 | 3721 | | 2002-04 | 8885 | | 2002-05 | 1824 | | 2002-06 | 1349 | | 2002-07 | 14619 | | 2002-08 | 3936 | | 2002-09 | 3391 | | 2002-10 | 20945 | | 2002-11 | 3767 | | 2002-12 | 5100 | | 2003-01 | 16071 | | 2003-02 | 3129 | | 2003-03 | 1508 | | 2003-04 | 8828 | | 2003-05 | 1860 | | 2003-06 | 1417 | | 2003-07 | 13697 | | 2003-08 | 2809 | | 2003-09 | 3539 | | 2003-10 | 13123 | | 2003-11 | 1510 | | 2003-12 | 2306 | | 2004-01 | 14942 | | 2004-02 | 12136 | | 2004-03 | 2431 | | 2004-04 | 9162 | | 2004-05 | 1530 | | 2004-06 | 1579 | | 2004-07 | 11479 | | 2004-08 | 7592 | | 2004-09 | 3595 | | 2004-10 | 19696 | | 2004-11 | 4373 | | 2004-12 | 6085 | | 2005-01 | 17858 | | 2005-02 | 3199 | | 2005-03 | 2037 | | 2005-04 | 5551 | | 2005-05 | 2637 | | 2005-06 | 1752 | | 2005-07 | 8176 | | 2005-08 | 5852 | | 2005-09 | 2476 | | 2005-10 | 8667 | | 2005-11 | 3452 | | 2005-12 | 1325 | | 2006-01 | 12860 | | 2006-02 | 2605 | | 2006-03 | 6791 | | 2006-04 | 7119 | | 2006-05 | 11131 | | 2006-06 | 4112 | | 2006-07 | 13759 | | 2006-08 | 3982 | | 2006-09 | 4038 | | 2006-10 | 18208 | | 2006-11 | 5510 | | 2006-12 | 5992 | | 2007-01 | 16546 | | 2007-02 | 3544 | | 2007-03 | 2002 | | 2007-04 | 6213 | | 2007-05 | 2225 | | 2007-06 | 1558 | | 2007-07 | 11587 | | 2007-08 | 2368 | | 2007-09 | 1340 | | 2007-10 | 6801 | | 2007-11 | 1876 | | 2007-12 | 1344 | | 2008-01 | 13196 | | 2008-02 | 2505 | | 2008-03 | 5294 | | 2008-04 | 7788 | | 2008-05 | 9594 | | 2008-06 | 2121 | | 2008-07 | 13299 | | 2008-08 | 3942 | | 2008-09 | 3504 | | 2008-10 | 19079 | | 2008-11 | 2924 | | 2008-12 | 7854 | | 2009-01 | 13756 | | 2009-02 | 8025 | | 2009-03 | 2356 | | 2009-04 | 6826 | | 2009-05 | 2542 | | 2009-06 | 1583 | | 2009-07 | 12273 | | 2009-08 | 2382 | | 2009-09 | 1724 | | 2009-10 | 5891 | | 2009-11 | 2445 | | 2009-12 | 1416 | | 2010-01 | 9044 | | 2010-02 | 6056 | | 2010-03 | 6845 | | 2010-04 | 8505 | | 2010-05 | 9093 | | 2010-06 | 4893 | | 2010-07 | 9863 | | 2010-08 | 8542 | | 2010-09 | 4337 | | 2010-10 | 27197 | | 2010-11 | 3658 | | 2010-12 | 7658 | | 2011-01 | 15965 | | 2011-02 | 3776 | | 2011-03 | 2467 | | 2011-04 | 6274 | | 2011-05 | 2975 | | 2011-06 | 2053 | | 2011-07 | 9595 | | 2011-08 | 5744 | | 2011-09 | 1796 | | 2011-10 | 7742 | | 2011-11 | 1756 | | 2011-12 | 877 | | 2012-01 | 13726 | | 2012-02 | 2983 | | 2012-03 | 6472 | | 2012-04 | 8575 | | 2012-05 | 8768 | | 2012-06 | 2727 | | 2012-07 | 14297 | | 2012-08 | 4769 | | 2012-09 | 4448 | | 2012-10 | 19847 | | 2012-11 | 5078 | | 2012-12 | 9474 | | 2013-01 | 20072 | | 2013-02 | 4320 | | 2013-03 | 2559 | | 2013-04 | 8867 | | 2013-05 | 3113 | | 2013-06 | 2432 | | 2013-07 | 14022 | | 2013-08 | 3106 | | 2013-09 | 2437 | | 2013-10 | 9967 | | 2013-11 | 3683 | | 2013-12 | 1802 | | 2014-01 | 85 | +---------+----------+ 511 rows in set (1.28 sec)

rkiddy commented 10 years ago

The warning on the import of decimal or integer values needs to be fixed by setting the '' values in the cd tables to NULL. This can be done in various ways, but it should be done so that it does not take a long time to process.

rkiddy commented 10 years ago

Since everything that is left has to do with the dates in the filer_filings table, I can close this as a dupe of issue # 5.