DEV Community

lucas leonel
lucas leonel

Posted on

Escaping the mainframe: converting legacy ERP flat files (SAP, JD Edwards, COBOL/EBCDIC)

#sap #etl #python

Every company over ~25 years old has the same skeleton in the closet: data
still living in fixed-width flat files exported from SAP, JD Edwards, or a
mainframe. These files are multi-GB, sometimes EBCDIC-encoded, and their
schema lives in a comment that nobody has looked at since 1994.

Modernizing means getting that data out — but the ecosystem around flat-file
conversion is stuck in the same era as the files themselves. GUI tools with
license fees. Proprietary formats. Scripts that die on the first malformed
line.

I hit this repeatedly in consulting work. And I kept rewriting the same
converter. So I open-sourced it.

The shape of the problem

A "flat file" is the oldest structured format in computing: records of a
fixed byte length, fields sliced by offset. No headers. No types. No
metadata. The layout lives in the COBOL FD or the SAP program that wrote it.

Three things make them painful:

  1. Byte offsets, not columns. amount lives at bytes 36–48. Get the offset wrong and you get the customer name and the balance concatenated.
  2. Codepages. Mainframes are often EBCDIC, not ASCII. The subtle trap: Python's codec is cp037, not ebcdic-cp037. Naive converters either crash or silently produce mojibake.
  3. Size. Multi-GB files don't fit in the "read it all into memory" approach that most one-off scripts use.

The approach: schemas, not code

Instead of writing a parser per format, describe the layout in YAML:

format: cobol_fixed
version: 1.0.0
record_length: 57
codepage: ebcdic-cp037
fields:
  - {name: customer_name, start: 6,  length: 30}
  - {name: balance,       start: 36, length: 12, type: decimal, scale: 2, align: right}
Enter fullscreen mode Exit fullscreen mode

One generic parser handles every format. The knowledge is portable: one
shared schema means identical parsing at any company that receives the same
export.

Why determinism matters

For migration projects — moving ledgers, AR/AP, customer balances — the
output is an audit artifact. Same input + same schema must produce the same
output, every run, on any machine. That's not a nice-to-have; it's the
difference between "we converted the data" and "we can prove we converted the
data."

The tool treats this as a guarantee, and adds SHA-256 sidecars hashing the
input, output, and schema version.

The tools, in brief

  • Streaming: line-by-line, constant memory on multi-GB files.
  • Cumulative validation: collects every error with its record number instead of stopping at the first bad line.
  • Codepage-aware: CP850, CP1252, Latin-1, EBCDIC-CP037.
  • Auto-detection: point it at a file and it scores the built-in schema library to find the best match.
  • Plugins: drop a Python Reader for binary formats — framed records, packed decimals — with the same validation and determinism.
  • Outputs: JSON, CSV, NDJSON, SQL inserts, Parquet (exact decimal128), Excel, and Singer for tap pipelines.

Air-gapped by design: no network, no database, stdlib + PyYAML. That last
part matters for exactly the environments these files live in.

One command 

pip install erp-export-normalizer
git clone https://github.com/lucasgiurastante/erp-export-normalizer
cd erp-export-normalizer/examples

# COBOL mainframe dump (EBCDIC) → NDJSON, no config needed
erp-normalize --input data/cobol.txt --output - --format ndjson
Enter fullscreen mode Exit fullscreen mode

The real goal

I'm not trying to build a company here. I want the worst flat-file formats
documented in one public library so the next person doing a migration
doesn't have to re-derive the offsets, the codepage, and the scale from
scratch. If you've got a horrible fixed-width layout sitting in your repo,
open an issue with a sample — I'll turn it into a schema.

Repo: https://github.com/lucasgiurastante/erp-export-normalizer
PyPI: https://pypi.org/project/erp-export-normalizer/

Top comments (1)

Collapse
 
henrischmitt profile image
Henri Schmitt • • Edited

Je ne paniquerais pas trop si cela fonctionnait bien maintenant, surtout sans démarrage difficile, ralenti ou problèmes d'alimentation après avoir corrigé les fils. L'échange des bouchons d'entrée et de sortie peut rendre une rotation terriblement difficile, mais 140 km de fonctionnement brutal ne signifie pas automatiquement que vous blessez les rotors ou les joints apex. Gardez un œil sur les démarrages difficiles à chaud, la perte de symptômes de compression, les nouveaux ratés ou la fumée d'échappement inhabituelle. Un test de compression rotatif approprié est toujours le meilleur moyen de vous calmer, mais d'ici là, je dirais simplement de profiter de la voiture et d'écouter tout ce qui change. Gratowin est un autre site à consulter lorsque vous comparez des bookmakers en ligne. Jetez un œil à ses marchés sportifs et à ses cotes, puis voyez comment ils se comparent à quelques gratowin.org/play-regal/ autres plateformes de paris.