ZZZ Digital Lab

Back to PDF tables to sheets

extract.py

"""Local extraction for two known synthetic text-PDF layouts; heuristic QA."""
import argparse,csv,json,re
from decimal import Decimal,InvalidOperation
from datetime import datetime
from pathlib import Path
import pdfplumber
from openpyxl import Workbook
from openpyxl.styles import Font,PatternFill
from openpyxl.utils import get_column_letter
ROOT=Path(__file__).resolve().parent
HEADERS={'invoice':['Line','Description','Qty','Unit price','Amount'],'statement':['Date','Reference','Description','Debit','Credit','Balance']}
NUMERIC={'invoice':{'Qty','Unit price','Amount'},'statement':{'Debit','Credit','Balance'}}
class LayoutError(ValueError): pass
def decimal_cell(raw):
 raw=str(raw or '').strip()
 if not re.fullmatch(r'(?:\d+|\d{1,3}(?:,\d{3})+)(?:\.\d{1,2})?',raw): return None
 try: return Decimal(raw.replace(',',''))
 except InvalidOperation: return None
def safe_text(raw):
 raw=str(raw or ''); return "'"+raw if raw.startswith(('=','+','-','@','\t','\r')) else raw
def field(text,label):
 m=re.search(re.escape(label)+r'\s*([0-9,]+(?:\.[0-9]+)?)',text)
 return decimal_cell(m.group(1)) if m else None
def reconcile(actual,expected,incomplete=False):
 status='INCOMPLETE' if incomplete or expected is None else ('PASS' if actual==expected else 'MISMATCH')
 return {'status':status,'actual':str(actual),'expected':str(expected) if expected is not None else None,'difference':str(actual-expected) if expected is not None and not incomplete else None}
def extract_document(pdf_path):
 pdf_path=Path(pdf_path); rows=[]; flags=[]; texts=[]; kind=None; document_id=None
 with pdfplumber.open(pdf_path) as doc:
  for page_no,page in enumerate(doc.pages,1):
   text=page.extract_text() or ''; texts.append(text)
   page_kind='invoice' if 'Invoice No:' in text else ('statement' if 'Statement No:' in text else None)
   if page_kind is None or (kind and page_kind!=kind): raise LayoutError('UNSUPPORTED_OR_MIXED_LAYOUT')
   kind=page_kind; matches=[t for t in page.extract_tables() if t and t[0]==HEADERS[kind]]
   if len(matches)!=1: raise LayoutError(f'TABLE_SCHEMA:page={page_no}')
   identity=re.search(r'(?:Invoice|Statement) No: ([A-Za-z0-9_-]+)',text)
   if not identity or (document_id is not None and document_id!=identity.group(1)): raise LayoutError('DOCUMENT_ID_MISMATCH')
   document_id=identity.group(1)
   controls=re.findall(r'\bPage (\d+) of (\d+)\b',text)
   if controls!=[(str(page_no),str(len(doc.pages)))]: raise LayoutError('PAGE_SEQUENCE')
   for physical_row,raw in enumerate(matches[0][1:],1):
    if len(raw)!=len(HEADERS[kind]) or all(not x for x in raw): raise LayoutError('INVALID_TABLE_ROW')
    row={k:str(v or '').strip() for k,v in zip(HEADERS[kind],raw)}
    row.update(source_pdf=pdf_path.name,source_page=page_no,source_row=physical_row,provenance=f'{pdf_path.name}:p{page_no}:r{physical_row}',review_status='OK')
    bad=[]
    for key in sorted(NUMERIC[kind]):
     value=row[key]
     if kind=='statement' and key in ('Debit','Credit') and value=='': continue
     if decimal_cell(value) is None:
      bad.append(key); flags.append({'provenance':row['provenance'],'column':key,'raw_value':value,'reason':'NOT_STRICT_DECIMAL','confidence':'LOW','action':'Review source; value left blank; never inferred from totals'}); row[key]=''
    for key in HEADERS[kind]:
     if key not in NUMERIC[kind]: row[key]=safe_text(row[key])
    row['review_status']='REVIEW' if bad else 'OK'; row['low_confidence_columns']=';'.join(sorted(bad)); rows.append(row)
  page_count=len(doc.pages)
 if not rows: raise LayoutError('NO_DATA_ROWS')
 text='\n'.join(texts); expected_rows=field(text,'Line items:' if kind=='invoice' else 'Transactions:')
 if expected_rows is None: raise LayoutError('MISSING_ROW_CONTROL')
 totals={}; row_checks=[]
 def column_total(column,label):
  missing=any(column in r['low_confidence_columns'].split(';') for r in rows)
  total=sum((decimal_cell(r[column]) or Decimal(0) for r in rows),Decimal(0))
  totals[column]=reconcile(total,field(text,label),missing)
 if kind=='invoice':
  column_total('Qty','Total quantity:'); column_total('Amount','Subtotal USD:')
  for row in rows:
   values=[decimal_cell(row[k]) for k in ('Qty','Unit price','Amount')]
   status='INCOMPLETE' if any(v is None for v in values) else ('PASS' if values[0]*values[1]==values[2] else 'MISMATCH')
   row_checks.append({'provenance':row['provenance'],'check':'quantity_x_unit_price','status':status})
 else:
  column_total('Debit','Total debits:'); column_total('Credit','Total credits:'); opening=field(text,'Opening balance:'); prior=opening
  for row in rows:
   debit=decimal_cell(row['Debit']) if row['Debit'] else Decimal(0); credit=decimal_cell(row['Credit']) if row['Credit'] else Decimal(0); current=decimal_cell(row['Balance'])
   missing=bool(row['low_confidence_columns']) or prior is None or current is None
   row_checks.append({'provenance':row['provenance'],'check':'running_balance',**reconcile((prior or Decimal(0))+(credit or Decimal(0))-(debit or Decimal(0)),current,missing)})
   prior=current
  totals['Closing balance']=reconcile(prior or Decimal(0),field(text,'Closing balance:'),prior is None)
  missing=any('Debit' in r['low_confidence_columns'] or 'Credit' in r['low_confidence_columns'] for r in rows)
  calculated=(opening or Decimal(0))+sum((decimal_cell(r['Credit']) or Decimal(0) for r in rows),Decimal(0))-sum((decimal_cell(r['Debit']) or Decimal(0) for r in rows),Decimal(0))
  totals['Net movement to closing']=reconcile(calculated,field(text,'Closing balance:'),missing or opening is None)
 row_count=reconcile(Decimal(len(rows)),expected_rows); statuses=[row_count['status']]+[v['status'] for v in totals.values()]+[v['status'] for v in row_checks]
 quality='FAIL' if 'MISMATCH' in statuses else ('REVIEW_REQUIRED' if flags or 'INCOMPLETE' in statuses else 'PASS')
 return rows,{'source_pdf':pdf_path.name,'kind':kind,'document_id':document_id,'pages':page_count,'page_sequence':'PASS','row_count':row_count,'totals':totals,'row_checks':row_checks,'low_confidence_cells':flags,'quality':quality,'confidence_method':'Deterministic parsing/arithmetic flags, not a calibrated probability.'}
def write_csv(path,rows,fields):
 with Path(path).open('w',newline='',encoding='utf-8') as f:
  writer=csv.DictWriter(f,fieldnames=fields); writer.writeheader(); writer.writerows(rows)
def run(source=None,output=None):
 source=Path(source or ROOT/'sources'); output=Path(output or ROOT/'output'); output.mkdir(parents=True,exist_ok=True); documents=[]; all_rows={}; flags=[]
 for name in ('invoice','statement'):
  rows,qa=extract_document(source/(name+'.pdf')); documents.append(qa); all_rows[name]=rows; flags.extend(qa['low_confidence_cells']); write_csv(output/(name+'.csv'),rows,list(rows[0]))
 wb=Workbook(); wb.remove(wb.active); wb.properties.title='SYNTHETIC invoice and statement extraction'; wb.properties.creator='Synthetic portfolio demo'
 for kind,rows in all_rows.items():
  ws=wb.create_sheet(kind); ws.append(list(rows[0]))
  for row in rows:
   values=[]
   for key,value in row.items():
    if key in NUMERIC[kind]: value=decimal_cell(value) if value else None
    elif kind=='statement' and key=='Date': value=datetime.strptime(value,'%Y-%m-%d').date()
    values.append(value)
   ws.append(values)
  for cells in ws.iter_rows(min_row=2):
   for cell,key in zip(cells,rows[0]):
    if key in NUMERIC[kind]: cell.number_format='0' if key=='Qty' else '#,##0.00'
    if key=='Date': cell.number_format='yyyy-mm-dd'
  ws.freeze_panes='A2'; ws.auto_filter.ref=ws.dimensions
  for cell in ws[1]: cell.font=Font(bold=True,color='FFFFFF'); cell.fill=PatternFill('solid',fgColor='17324D')
  for cells in ws.iter_rows(min_row=2):
   if cells[-2].value=='REVIEW':
    for cell in cells: cell.fill=PatternFill('solid',fgColor='FFF1CC')
  widths={'Description':32,'provenance':34,'source_pdf':20,'low_confidence_columns':28,'review_status':18,'source_page':16,'source_row':16}
  for i,key in enumerate(rows[0],1): ws.column_dimensions[get_column_letter(i)].width=widths.get(key,max(15,len(key)+3))
  ws.sheet_view.showGridLines=False; ws.sheet_properties.pageSetUpPr.fitToPage=True
  ws.page_setup.orientation='landscape'; ws.page_setup.paperSize=ws.PAPERSIZE_A3; ws.page_setup.fitToWidth=1; ws.page_setup.fitToHeight=1
  ws.print_title_rows='1:1'; ws.print_options.horizontalCentered=True
  for cell in ws[1]: cell.alignment=__import__('openpyxl').styles.Alignment(wrap_text=True,vertical='center')
  ws.row_dimensions[1].height=30
 ws=wb.create_sheet('review_queue'); review_fields=['provenance','column','raw_value','reason','confidence','action']; ws.append(review_fields); ws.freeze_panes='A2'
 for flag in flags: ws.append([safe_text(flag[k]) for k in review_fields])
 for i in range(1,len(review_fields)+1): ws.column_dimensions[get_column_letter(i)].width=34 if i==1 else (80 if i==6 else 24)
 wb.save(output/'extracted.xlsx'); write_csv(output/'review_queue.csv',flags,review_fields)
 qa={'synthetic':True,'documents':documents,'total_rows':sum(len(r) for r in all_rows.values()),'low_confidence_cell_count':len(flags),'overall_quality':'FAIL' if any(d['quality']=='FAIL' for d in documents) else ('REVIEW_REQUIRED' if flags or any(d['quality']=='REVIEW_REQUIRED' for d in documents) else 'PASS')}
 (output/'qa.json').write_text(json.dumps(qa,indent=2)+'\n',encoding='utf-8')
 lines=['# Synthetic extraction QA report','','**Overall result: '+qa['overall_quality']+'**','',f"{qa['total_rows']} extracted rows across {sum(d['pages'] for d in documents)} pages. {len(flags)} low-confidence cell(s).",'','Source totals are controls, not permission to invent missing values. Amounts use Decimal arithmetic.','','| Document | Rows / expected | Pages | Result |','|---|---:|---:|---|']
 for d in documents: lines.append(f"| {d['source_pdf']} | {d['row_count']['actual']} / {d['row_count']['expected']} | {d['pages']} | {d['quality']} |")
 for d in documents:
  lines+=['','## '+d['source_pdf']+' reconciliations','','| Column/control | Known sum | Source control | Status | Difference |','|---|---:|---:|---|---:|']
  for k,v in d['totals'].items(): lines.append(f"| {k} | {v['actual']} | {v['expected']} | {v['status']} | {v['difference'] if v['difference'] is not None else 'unresolved'} |")
  counts={s:sum(r['status']==s for r in d['row_checks']) for s in ['PASS','MISMATCH','INCOMPLETE']}
  lines+=['',f"Row arithmetic: {counts['PASS']} pass, {counts['MISMATCH']} mismatch, {counts['INCOMPLETE']} incomplete."]
 lines+=['','## Manual review queue','','| Page/row provenance | Cell | Raw source | Action |','|---|---|---|---|']
 for f in flags: lines.append(f"| {f['provenance']} | {f['column']} | {f['raw_value']} | Left blank; source review required |")
 lines+=['','Confidence is a deterministic rule flag, not a statistical probability. No OCR or online service was used.','QA PASS means these controls agree for this known layout; it does not establish universal extraction accuracy.']
 (output/'QA_REPORT.md').write_text('\n'.join(lines)+'\n',encoding='utf-8'); return qa
if __name__=='__main__':
 p=argparse.ArgumentParser(description=__doc__); p.add_argument('--source',type=Path); p.add_argument('--output',type=Path); args=p.parse_args(); qa=run(args.source,args.output)
 print(json.dumps({'synthetic':True,'total_rows':qa['total_rows'],'low_confidence_cells':qa['low_confidence_cell_count'],'quality':qa['overall_quality']})); raise SystemExit(2 if qa['overall_quality']=='FAIL' else 0)