CSV Data Sanitization & UTF-8 Preflight Validator Guide

2. Four Field-Level Preflight Rules (Finance & Accounting — Vendor W-9, 1099 & Fee Schedules)

Required Row-1 Header Schema: vendor_id,legal_business_name,ein_last4,billing_contact,street_address,city_state_zip,annual_cap_usd

• Leading-Zero Preservation (ein_last4 & ZIP): Format EIN/TIN and New England ZIP codes ('02110') as Text so Excel does not truncate leading zeros.
• Legal Entity Commas (legal_business_name): Wrap names like '"Northstar Logistics, LLC"' in double quotes so commas do not shift columns.
• UTF-8 BOM Removal: Strip any hidden 3-byte Byte Order Mark ('\xef\xbb\xbf') from column 1 ('vendor_id') so AcroForm field matching succeeds.
• Row Count Preflight: Verify up to 250 data rows per batch file with no blank trailing lines at the end of the CSV.

vendor_id,legal_business_name,ein_last4,billing_contact,street_address,city_state_zip,annual_cap_usd
"VND-0091","Northstar Logistics, LLC","0482","ap@northstarlogistics.example","100 Harbor Blvd, Suite 400","Boston, MA 02110","85,000.00"
"VND-0092","Apex Industrial Supply, Inc.","7910","billing@apexsupply.example","450 West Loop N","Houston, TX 77024","120,000.00"

# Local CLI Preflight: Strip UTF-8 BOM, trim header whitespace & verify row count
python3 -c "
import csv, sys
with open('input.csv', 'r', encoding='utf-8-sig', newline='') as f:
    rows = list(csv.reader(f))
headers = [h.strip() for h in rows[0]]
print('Cleaned Headers:', ','.join(headers))
print('Data Rows Ready:', len(rows) - 1, '(Max 250 per FormBatch run)')
with open('sanitized_utf8.csv', 'w', encoding='utf-8', newline='') as out:
    w = csv.writer(out, quoting=csv.QUOTE_MINIMAL)
    w.writerow(headers)
    w.writerows([r for r in rows[1:] if any( cell.strip() for cell in r )])
"