File and Batch Interfaces

The least fashionable and most durable integration style. Banks, payroll providers, government agencies, and 3PLs all still exchange files, and they will still be doing so in ten years.

Why files persist

Banks and government systems mandate them
Robust to outages — the file is still there tomorrow
Easy to inspect, archive, and replay
No availability coupling between the two systems
Business users can produce and consume them (Excel)
High volume is cheap

Formats

Format	Notes
CSV	Ubiquitous, deceptively problematic — quoting, embedded newlines, encoding, delimiter conflicts
Fixed width	Banks and legacy mainframes. Byte-position based, unforgiving, but unambiguous
Excel (xlsx)	What users actually send. Dates, leading zeros, and number formatting will hurt you
XML	Structured, validatable with XSD, verbose. ISO 20022, e-invoicing
JSON	Modern, no standard schema validation unless you add it
X12 / EDIFACT	See EDI
Parquet	Analytics bulk loads, columnar, compressed

The CSV traps

Every one of these has caused a production incident somewhere:

Delimiter in the data:       "Rahman Textiles, Ltd"  → must be quoted
Quotes in the data:          "The ""Blue"" Line"      → escaped
Newlines in a field:         multi-line addresses     → must be quoted
Encoding:                    UTF-8 vs Latin-1 vs UTF-16 vs UTF-8-with-BOM
Line endings:                CRLF vs LF
Leading zeros stripped:      material 0001234 → 1234  (Excel's fault, always)
Long numbers → scientific:   8901234567890 → 8.90123E+12
Dates reformatted:           15/03/2026 → 03/15/2026 → 2026-03-15, silently
Locale decimal separator:    1.234,56 vs 1,234.56
Trailing whitespace:        "DHK1 " ≠ "DHK1"
No header, or a changed header order
A blank line at the end producing a phantom empty record

Rules that prevent most of them:

Always UTF-8, and document it. Handle the BOM.
Always quote every field, not just those needing it.
ISO 8601 dates (2026-03-15) only.
Decimal point, never comma, with no thousands separator.
Treat all identifiers as strings, never numbers.
Validate the header row matches the expected contract before processing anything.
Reject the file on a checksum or record-count mismatch, don’t process it partially.

File naming and control

INVOICES_1000_20260315_001.csv
   │      │      │       └── sequence within the day
   │      │      └────────── date
   │      └───────────────── company code
   └──────────────────────── content type

Control/trailer records prove completeness:

HDR|INVOICES|1000|20260315|001
DTL|INV-001|C-100455|11500.00|BDT
DTL|INV-002|C-100456|22300.00|BDT
...
TRL|1247|14882140.00
    │        └── hash total of the amount column
    └──────────── record count

Always validate the trailer before processing. A truncated file that processes 800 of 1,247 invoices and reports success is a genuinely bad day. The record count and hash total catch it instantly.

Transport

Method	Notes
SFTP	The standard. Key-based auth, not passwords
FTPS	Older, firewall-hostile
Plain FTP	Never. Credentials and data in clear text
AS2	EDI, with signed receipts
HTTPS upload/download	Simple, works with modern auth
Cloud storage (S3, Blob, GCS)	Increasingly the default; use event notifications
Email attachment	Common, fragile, insecure. Avoid for anything structured
EBICS	European banking standard

The partial-file race

Sender is still writing INVOICES.csv
Receiver's poller sees the file and starts reading
→ processes half a file

Solutions:

Write to a temp name, then rename — rename is atomic on most filesystems. The best answer.
Marker/trigger file — write INVOICES.csv, then INVOICES.done; the poller waits for the marker
File size stability check — poll until the size stops changing. Crude, works
Event-driven — cloud storage notifications fire on completed upload

This is the most common file-interface bug. Design against it explicitly.

Processing pipeline

POLL / receive          detect a complete file
MOVE to processing      atomic move out of the inbound folder — prevents double pickup
ARCHIVE the raw file    before any parsing. You will need the original
PARSE                   syntax errors → reject the whole file
VALIDATE control totals record count, hash totals
VALIDATE records        business rules, per record
LOAD                    per record or in chunks
REPORT                  processed / failed with reasons
ACKNOWLEDGE             a response file, an API call, or an email
MOVE to done / error

Folder structure

/inbound/           files land here
/inbound/processing/ moved here on pickup (atomic move prevents double processing)
/archive/2026/03/15/ raw files, retained
/error/             failed files, with an error report alongside
/outbound/          files we produce
/outbound/sent/     after successful transmission

Error handling: all-or-nothing vs per-record

ALL-OR-NOTHING            PER-RECORD
Any error → reject file   Good records load, bad ones go to an error file
✅ Consistent             ✅ Progress; one bad row doesn't block 10,000 good ones
❌ 1 bad row blocks all   ❌ Partial state; needs reconciliation

Choose per interface, and document it.

Bank statements: all-or-nothing (a partial statement corrupts the reconciliation)
Master data loads: per record (load the 9,997 good customers)
Payment files: all-or-nothing (partial payment runs are unacceptable)
Sales order imports: per record, with a clear error file back to the sender

Reprocessing

Files get reprocessed: an error was fixed, a load was interrupted, a partner resent.

Requirements:

Idempotency — reprocessing must not duplicate. Key on (file name + record key) or the business key
Duplicate file detection — hash the file; if you’ve seen this hash, warn loudly
Partial reprocess — reload only the previously failed records
Audit — who reprocessed, when, why

file_load(
  id, interface_name, file_name, file_hash, received_at,
  record_count, records_loaded, records_failed,
  status, processed_at, processed_by,
  UNIQUE(interface_name, file_hash)   -- duplicate file detection
);
file_load_record(
  load_id, line_number, business_key, status, error_message, entity_id
);

Excel imports

Business users will send spreadsheets. Design for it rather than fighting it.

Good practice:

Provide a template with the exact columns, data types, and validation
Validate the header before reading data
Read cells as text, then parse deliberately — never trust Excel’s typing
Return an annotated error file: the user’s own spreadsheet with an added “Error” column and the failing cells highlighted. They fix it and re-upload. This one feature makes an import tool genuinely usable.
Preview before commit — show the first 20 parsed rows and the totals
Size limits and a row cap

Bank file formats

Worth a specific mention because they’re strict, non-negotiable, and everywhere:

Format	Use
ISO 20022 pain.001	Credit transfer initiation (XML)
pain.008	Direct debit initiation
camt.053 / .052 / .054	Statement / intraday / notification
MT940 / MT942	SWIFT statement (semi-structured text)
BAI2	US bank statements
NACHA	US ACH (fixed width)
Local formats	BEFTN (Bangladesh), NEFT/RTGS (India), and many others

Banks reject files for a single character out of place. Always validate against the bank’s XSD before sending, and test with the bank’s validation tool if they provide one.

Monitoring

Check	Alert
Expected file did not arrive by the SLA time	Critical — silent failure is the worst kind
File arrived but failed validation	Error
Record count deviates >20% from the usual	Warning — something changed upstream
Error records above threshold	Warning
Outbound transmission failed	Error
Archive folder growth	Capacity
SFTP key or certificate expiry	30 days ahead

“The file didn’t arrive” is the most important alert. A missing file produces no error anywhere; the system simply does nothing, and nobody notices until month-end. Every scheduled inbound interface needs an expected-arrival monitor.