{"action":"created","issue":{"url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/265","labels_url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/265/labels{/name}","comments_url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/265/comments","events_url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/265/events","html_url":"https://github.com/california-civic-data-coalition/django-calaccess-raw-data/pull/265","id":67488372,"number":265,"title":"Id columns included in models, and beginning of additional verifications...","user":{"login":"rkiddy","id":691368,"avatar_url":"https://avatars.githubusercontent.com/u/691368?v=3","gravatar_id":"","url":"https://api.github.com/users/rkiddy","html_url":"https://github.com/rkiddy","followers_url":"https://api.github.com/users/rkiddy/followers","following_url":"https://api.github.com/users/rkiddy/following{/other_user}","gists_url":"https://api.github.com/users/rkiddy/gists{/gist_id}","starred_url":"https://api.github.com/users/rkiddy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rkiddy/subscriptions","organizations_url":"https://api.github.com/users/rkiddy/orgs","repos_url":"https://api.github.com/users/rkiddy/repos","events_url":"https://api.github.com/users/rkiddy/events{/privacy}","received_events_url":"https://api.github.com/users/rkiddy/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-04-10T01:57:02Z","updated_at":"2015-04-11T00:58:55Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/pulls/265","html_url":"https://github.com/california-civic-data-coalition/django-calaccess-raw-data/pull/265","diff_url":"https://github.com/california-civic-data-coalition/django-calaccess-raw-data/pull/265.diff","patch_url":"https://github.com/california-civic-data-coalition/django-calaccess-raw-data/pull/265.patch"},"body":"I am sure this my use of python idiom is not good, and there are definitely better ways to do some of this. But the verification shows you some things.\r\n\r\nFor example, see below. The backslash at the end of line 84793 is messing up the loading of the next line, so a row with id of 84793 does not appear in the table.\r\n\r\n```\r\n$ python manage.py verifycalaccessrawfile HdrCd\r\n Verifying HdrCd\r\n  Table Record count doesn't match CSV or TSV.\r\n    table # 638389  CSV # 638390  TSV # 638390\r\n    ids: [84793]\r\n\r\n    hdr: id\\tFILING_ID\\tAMEND_ID\\tREC_TYPE\\tEF_TYPE\\tSTATE_CD\\tCAL_VER\\tSOFT_NAME\\tSOFT_VER\\tHDRCOMMENT\\r\\n\r\n    tsv: 84792\\t937738\\t0\\tHDR\\tCAL\\tCA\\t2.01\\tCOMPLETECAMPAIGNS.COM\\t1.00\\\\\\t\\r\\n\r\n    tsv: 84793\\t930987\\t1\\tHDR\\tCAL\\tCA\\t2.01\\tNETFILE\\t1611\\t\\r\\n\r\n    tsv: 84794\\t937770\\t0\\tHDR\\tCAL\\tCA\\t2.01\\tEASYDISCLOSURE.COM\\t1.00\\t\\r\\n\r\n```\r\nIn my scripts, I found it most useful to store info about the differences and then use another script to display the differences. Sometimes it is useful to see one-line on a line, as I have it above, but sometimes it is useful to break each line into the separate column values and that sometimes make the error more obvious.\r\n\r\nFor example, see below. I think the problem is from the middle row, where there seems to be a tab in the middle of a name:\r\n```\r\n\\tSteve Mohr,\\t Senior Vice President/CFO\\t\r\n```\r\nBut one would have to see which value corresponded to which column header to see where the problem lies.\r\n\r\n```\r\n$ python manage.py verifycalaccessrawfile CvrCampaignDisclosureCd\r\n Verifying CvrCampaignDisclosureCd\r\n  Table Record count doesn't match CSV or TSV.\r\n    table # 330593  CSV # 330594  TSV # 330602\r\n    ids: [340, 471, 731, 1781, 109223, 155368, 167226, 276377, 318903]\r\n...\r\n    hdr: id\\tFILING_ID\\tAMEND_ID\\tREC_TYPE\\tFORM_TYPE\\tFILER_ID\\tENTITY_CD\\tFILER_NAML\\tFILER_NAMF\\tFILER_NAMT\\tFILER_NAMS\\tREPORT_NUM\\tRPT_DATE\\tSTMT_TYPE\\tLATE_RPTNO\\tFROM_DATE\\tTHRU_DATE\\tELECT_DATE\\tFILER_CITY\\tFILER_ST\\tFILER_ZIP4\\tFILER_PHON\\tFILER_FAX\\tFILE_EMAIL\\tMAIL_CITY\\tMAIL_ST\\tMAIL_ZIP4\\tTRES_NAML\\tTRES_NAMF\\tTRES_NAMT\\tTRES_NAMS\\tTRES_CITY\\tTRES_ST\\tTRES_ZIP4\\tTRES_PHON\\tTRES_FAX\\tTRES_EMAIL\\tCMTTE_TYPE\\tCONTROL_YN\\tSPONSOR_YN\\tPRIMFRM_YN\\tBRDBASE_YN\\tAMENDEXP_1\\tAMENDEXP_2\\tAMENDEXP_3\\tRPT_ATT_CB\\tCMTTE_ID\\tREPORTNAME\\tRPTFROMDT\\tRPTTHRUDT\\tEMPLBUS_CB\\tBUS_NAME\\tBUS_CITY\\tBUS_ST\\tBUS_ZIP4\\tBUS_INTER\\tBUSACT_CB\\tBUSACTVITY\\tASSOC_CB\\tASSOC_INT\\tOTHER_CB\\tOTHER_INT\\tCAND_NAML\\tCAND_NAMF\\tCAND_NAMT\\tCAND_NAMS\\tCAND_CITY\\tCAND_ST\\tCAND_ZIP4\\tCAND_PHON\\tCAND_FAX\\tCAND_EMAIL\\tBAL_NAME\\tBAL_NUM\\tBAL_JURIS\\tOFFICE_CD\\tOFFIC_DSCR\\tJURIS_CD\\tJURIS_DSCR\\tDIST_NO\\tOFF_S_H_CD\\tSUP_OPP_CD\\tEMPLOYER\\tOCCUPATION\\tSELFEMP_CB\\tBAL_ID\\tCAND_ID\\r\\n\r\n...\r\n    tsv: 276376\\t1819136\\t0\\tCVR\\tF460\\t1353812\\tCTL\\tJONES-SAWYER FOR ASSEMBLY 2014\\t\\t\\t\\t000\\t1/31/2014 12:00:00 AM\\tSA\\t\\t7/1/2013 12:00:00 AM\\t12/31/2013 12:00:00 AM\\t\\tLOS ANGELES\\tCA\\t90010\\t213-489-4792\\t213-489-4818\\tdlgould@davidgouldcomapny.com\\t\\t\\t\\tGould\\tDavid L.\\t\\t\\tLos Angeles\\tCA\\t90010\\t213-489-4792\\t\\t\\tC\\tY\\tN\\tN\\tN\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\tJones-Sawyer\\tReginald B.\\t\\t\\tLos Angeles\\tCA\\t90010\\t213-489-4792\\t213-489-4818\\tdlgould@davidgouldcompany.com\\t\\t\\t\\tASM\\t\\tASM\\t\\t59\\tS\\t\\t\\t\\t\\t\\t\\r\\n\r\n    tsv: 276377\\t1819137\\t0\\tCVR\\tF461\\t484219\\tMDI\\tLoma Linda University Medical Center\\t\\t\\t\\t000\\t1/21/2014 12:00:00 AM\\t\\t\\t1/1/2013 12:00:00 AM\\t12/31/2013 12:00:00 AM\\t\\tLoma Linda\\tCA\\t92354\\t(909) 558-4728\\t\\t\\t\\t\\t\\tSteve Mohr,\\t Senior Vice President/CFO\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\tX\\tHospital/Health Care\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\r\\n\r\n    tsv: 276378\\t1819163\\t0\\tCVR\\tF460\\t1353849\\tCTL\\tGALGIANI FOR SENATE 2016\\t\\t\\t\\t000\\t1/30/2014 12:00:00 AM\\tSA\\t\\t7/1/2013 12:00:00 AM\\t12/31/2013 12:00:00 AM\\t6/7/2016 12:00:00 AM\\tLos Angeles\\tCA\\t90010\\t213 489-4792\\t213 489-4818\\tdlgould@davidgouldcompany.com\\t\\t\\t\\tGould\\tDavid\\t\\t\\tLos Angeles\\tCA\\t90010\\t213 489-4792\\t\\t\\tC\\tY\\tN\\tN\\tN\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\t\\tGalgiani\\tCathleen\\t\\t\\tManteca\\tCA\\t95336\\t213 489-4792\\t213 489-4818\\t\\t\\t\\t\\tSEN\\t\\tSEN\\t\\t5\\t\\t\\t\\t\\t\\t\\t\\r\\n\r\n```"},"comment":{"url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/comments/91733451","html_url":"https://github.com/california-civic-data-coalition/django-calaccess-raw-data/pull/265#issuecomment-91733451","issue_url":"https://api.github.com/repos/california-civic-data-coalition/django-calaccess-raw-data/issues/265","id":91733451,"user":{"login":"rkiddy","id":691368,"avatar_url":"https://avatars.githubusercontent.com/u/691368?v=3","gravatar_id":"","url":"https://api.github.com/users/rkiddy","html_url":"https://github.com/rkiddy","followers_url":"https://api.github.com/users/rkiddy/followers","following_url":"https://api.github.com/users/rkiddy/following{/other_user}","gists_url":"https://api.github.com/users/rkiddy/gists{/gist_id}","starred_url":"https://api.github.com/users/rkiddy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rkiddy/subscriptions","organizations_url":"https://api.github.com/users/rkiddy/orgs","repos_url":"https://api.github.com/users/rkiddy/repos","events_url":"https://api.github.com/users/rkiddy/events{/privacy}","received_events_url":"https://api.github.com/users/rkiddy/received_events","type":"User","site_admin":false},"created_at":"2015-04-11T00:58:55Z","updated_at":"2015-04-11T00:58:55Z","body":"I took out all the stuff that was trying to read the lines in the TSV file and the CSV file and am storing the missing lines in a \"verification\" table. Another command, or the verify command called with different flags, can read this data and get the text files and do either just the lines, or breaking out all the values depending on how the flags passed."}}