Import AP Ledger Data from FOIA 26-117 #43

New issue

Open

opened 2026-02-13 04:23:24 +00:00 by lewismoten · 0 comments

lewismoten commented

2026-02-13 04:23:24 +00:00

(Migrated from github.com)

Maybe hash the check number, amount, and vendor ID? The issue is that if transactions are reclassified, the account number (dept, function, description) may change. May need some research on the best way forward. Data may be locked, in which case only Journal entries (FOIA 26-140) can apply changes after approval.

The main goal is to make this a common process that can upload a month (or a few months) of data going forward without worry of duplicating or corrupting data.

Observations:
First four rows are a header, including the report name AP308 with word-wrapping for column names

AP308,,WARREN COUNTY BOARD OF SU, A/P REGULAR,CHECK REGISTER, TIME-16:36:37,, PAGE,1,,,
,, WARREN COUNTY,,,,,,,,,
P/O,VEND., VENDOR, INVOICE, INVOICE, ACCOUNT,, NET,CHECK, CHECK,,
NO., NO., NAME, NO., DATE, NO.,ACCT PD, AMOUNT, NO., DATE, DESCRIPTION,BATCH

P/O NO. - Purchase Order Number
VEND. NO. - Vendor Number
VENDOR NAME - Vendor Name
INVOICE NO. - Invoice Number - often includes the external account number and date. Duplicate months will have something added to keep them unique. Month is often Mmm-YY, but not fixed in the formatting. Sometimes invoice number is just the month/year for the billing period.
INVOICE DATE - Invoice Date - M/D/YYYY
ACCOUNT NO. - Account Number - GL Code that needs to be broken down and mapped to chart of accounts. ie 4001-012150-2006- - -
ACCT PD - Account Paid Date - YYYY/MM
NET AMOUNT - How much was paid - Signed Decimal(10,2), negative values are credits/adjustments
CHECK NO. - Check Number. A vendor may receive two payments on the same check.
CHECK DATE - Date the check was issued - M/DD/YYYY
DESCRIPTION - Description of the transaction. This usually maps the description in the chart of accounts, but sometimes differs
BATCH - The batch number in which checks were issued

Most valid lines start with a P/O NO. of 0000000, or a valid number. If the first value on a line is blank before the first delimiter, the line is likely a rollup of check totals, or a second header line. If the first value is AP308 (often on the check total line), it is a header.

Review the following CSV with a valid line of data, check total, 2 header lines, and another line of data for an example

0000000,005099,MOTOROLA SOLUTIONS CREDIT,1011271963  7/13, 7/01/2013,   4001-032000-3011-   -   -,2013/07,86027.64,254413, 7/17/2013,      INSPECTION SERVICES, 09711
,,,,,                      CHECK  TOTAL,,86027.64,,,,
AP308,,WARREN COUNTY BOARD OF SU,        A/P REGULAR,CHECK REGISTER,  TIME-16:36:37,,      PAGE,5,,,
,, WARREN COUNTY,,,,,,,,,
0000000,001666,NAPA AUTO PARTS,557951, 7/01/2013,   4001-071030-5402-   -   -,2013/07,15.18,254415, 7/17/2013,      FOOD SUPPLIES, 09693

The last line in each exported file has grand summary lines as well:

0000000,020055,QUILL,577442, 5/12/2014,   4001-011010-5401-   -   -,2014/06,23.98,259226, 6/18/2014,      OFFICE SUPPLIES, 00342
,,,,,                      CHECK  TOTAL,,23.98,,,,
,,,,,                  CHECK TYPE TOTAL,,-66820.75,,,,
,,,,,                       FINAL TOTAL,23082018.6,,,,,

The main thing to keep in mind is to ignore lines where the first value is blank or contains anything other than digits.

See /utils/convertApRegister.js and improve where necessary.

Work on a method to process all files from 2014 to the present (FOIA 26-117) and determine whether existing data can be detected/avoided from being overwritten, or if it will be overwritten. Need to ensure dupe records are not created. Take all the chart of accounts lookups into consideration. Maybe hash the check number, amount, and vendor ID? The issue is that if transactions are reclassified, the account number (dept, function, description) may change. May need some research on the best way forward. Data may be locked, in which case only Journal entries (FOIA 26-140) can apply changes after approval. The main goal is to make this a common process that can upload a month (or a few months) of data going forward without worry of duplicating or corrupting data. Observations: First four rows are a header, including the report name `AP308` with word-wrapping for column names AP308,,WARREN COUNTY BOARD OF SU, A/P REGULAR,CHECK REGISTER, TIME-16:36:37,, PAGE,1,,, ,, WARREN COUNTY,,,,,,,,, P/O,VEND., VENDOR, INVOICE, INVOICE, ACCOUNT,, NET,CHECK, CHECK,, NO., NO., NAME, NO., DATE, NO.,ACCT PD, AMOUNT, NO., DATE, DESCRIPTION,BATCH * P/O NO. - Purchase Order Number * VEND. NO. - Vendor Number * VENDOR NAME - Vendor Name * INVOICE NO. - Invoice Number - often includes the external account number and date. Duplicate months will have something added to keep them unique. Month is often Mmm-YY, but not fixed in the formatting. Sometimes invoice number is just the month/year for the billing period. * INVOICE DATE - Invoice Date - M/D/YYYY * ACCOUNT NO. - Account Number - GL Code that needs to be broken down and mapped to chart of accounts. ie `4001-012150-2006- - -` * ACCT PD - Account Paid Date - YYYY/MM * NET AMOUNT - How much was paid - Signed Decimal(10,2), negative values are credits/adjustments * CHECK NO. - Check Number. A vendor may receive two payments on the same check. * CHECK DATE - Date the check was issued - M/DD/YYYY * DESCRIPTION - Description of the transaction. This usually maps the description in the chart of accounts, but sometimes differs * BATCH - The batch number in which checks were issued * Most valid lines start with a P/O NO. of `0000000`, or a valid number. If the first value on a line is blank before the first delimiter, the line is likely a rollup of check totals, or a second header line. If the first value is `AP308` (often on the check total line), it is a header. Review the following CSV with a valid line of data, check total, 2 header lines, and another line of data for an example ```csv 0000000,005099,MOTOROLA SOLUTIONS CREDIT,1011271963 7/13, 7/01/2013, 4001-032000-3011- - -,2013/07,86027.64,254413, 7/17/2013, INSPECTION SERVICES, 09711 ,,,,, CHECK TOTAL,,86027.64,,,, AP308,,WARREN COUNTY BOARD OF SU, A/P REGULAR,CHECK REGISTER, TIME-16:36:37,, PAGE,5,,, ,, WARREN COUNTY,,,,,,,,, 0000000,001666,NAPA AUTO PARTS,557951, 7/01/2013, 4001-071030-5402- - -,2013/07,15.18,254415, 7/17/2013, FOOD SUPPLIES, 09693 ``` The last line in each exported file has grand summary lines as well: ```csv 0000000,020055,QUILL,577442, 5/12/2014, 4001-011010-5401- - -,2014/06,23.98,259226, 6/18/2014, OFFICE SUPPLIES, 00342 ,,,,, CHECK TOTAL,,23.98,,,, ,,,,, CHECK TYPE TOTAL,,-66820.75,,,, ,,,,, FINAL TOTAL,23082018.6,,,,, ``` The main thing to keep in mind is to ignore lines where the first value is blank or contains anything other than digits. See /utils/convertApRegister.js and improve where necessary.