Transforming Files Between Systems

Two systems can both be completely correct and still unable to read each other's files. The warehouse system exports exactly what it has always exported: semicolon-delimited, a legacy single-byte character encoding, no header row, Windows line endings. The analytics platform ingests exactly what it documents: comma-separated, UTF-8, header required, Unix line endings. Neither is broken. They just never met — and the file caught between them is your problem now.

Transformation is the pipeline stage that resolves the disagreement. It is a deliberate, minimal reshaping of a file from the form the source produces into the form the destination accepts. It deserves more respect than it usually gets, because it is the one stage whose whole job is to change the file. That makes it the one stage that can silently damage data. A botched encoding conversion corrupts every customer name in the file while the row counts still reconcile perfectly.

This article inventories the transforms that appear in almost every flow — encodings, line endings, delimiters, headers, value mappings. It shows each one straight, with before-and-after samples of what going wrong looks like. It finishes with the discipline that keeps transforms safe: small steps, golden-file tests, and logs that count everything. It is part of our pre- and post-processing series. In the skeleton from the anatomy of a transfer pipeline, this is the transform stage — the reshaping half of it. Packaging is covered separately in the compression article.

The Transform Inventory

Nearly every transform you will ever need falls into a short list. The table names each one, why it comes up, and the classic way it goes wrong when done naively. The rest of the article expands the dangerous ones:

Notice what is not on the list: business logic. Recalculating amounts, merging records, enriching from databases — that is application work. Hiding it inside a transfer pipeline is how organizations end up with critical logic nobody can find. A pipeline transform changes representation, not meaning.

Encodings, Told Straight

A character encoding is the mapping between characters and the bytes that store them. Plain unaccented letters and digits are stored identically almost everywhere. That is why encoding bugs hide until the first name with an accent, an em dash, or a non-Latin character walks through the flow. Beyond that shared core, two families matter in file exchange. The first is legacy single-byte code pages — one byte per character, with a different regional table deciding what the upper half of values means. The second is UTF-8, the variable-length encoding that can represent essentially every character and has become the default interchange format.

When bytes written under one mapping are read under another, you get mojibake — the classic garbled text. José saved as UTF-8 and read as a legacy code page displays as José; the reverse mistake shows a replacement marker or drops the character entirely. The corruption sits only in the affected characters, so counts and totals reconcile and validation layers that never look at text pass happily. Conversion itself is one command on any system with standard tools:

# convert from the source system's code page to UTF-8
# (name the actual source encoding your export uses)
iconv -f <source-code-page> -t UTF-8 in.csv > out_utf8.csv

# strict check: exits non-zero if the result is not valid UTF-8
iconv -f UTF-8 -t UTF-8 out_utf8.csv > /dev/null

The two traps are worse than the conversion. First, double conversion: a file is converted twice because two steps each "helpfully" convert, or a rerun re-converts. The file is garbled in a way that is genuinely painful to undo (é becomes é becomes é). Convert exactly once, at one named step, and make that step refuse input that already passes a strict UTF-8 decode. Second, lossy conversion: going from UTF-8 down to a legacy code page, characters outside the target's table simply have no representation. Converters offer three behaviors — fail, drop, or approximate — and for a pipeline the right setting is fail, loudly, so a human decides. Silently shipping Müller as M?ller to a payments system is not a rounding error.

One more encoding artifact worth naming: the BOM (byte-order mark), an invisible marker some tools write at the start of UTF-8 files. Some consumers require it; many choke on it — the tell-tale symptom is a first column mysteriously named store_id. Whether the file carries a BOM belongs in the file contract from the validation article. Adding or stripping it is a one-line, deliberate transform — not something left to whatever each tool feels like.

Line Endings: The Invisible Difference

Text files mark the end of each line with control characters. The two conventions split along OS lines: CRLF (carriage return plus line feed, the Windows convention) and LF (line feed alone, the Unix convention). Most editors display both identically, which is exactly the problem — two files can look the same, differ on every single line, and parse differently.

What actually breaks: a strict parser reading a CRLF file as LF sees the carriage return as part of the last field. So 41.85 becomes 41.85\r and fails a numeric check — or worse, passes a string comparison until someone queries for it. Fixed-width layouts shift by one character per line. Row counts disagree between systems. And any conversion changes every line of the file, which means its checksum no longer matches the original's. So integrity verification must compare against the file you actually sent, a sequencing point covered in verifying transfers end to end. Here is the difference made visible, with the conversion each direction needs:

before: CRLF endings (shown explicitly)      after: LF endings
store_id,qty,amount\r\n                      store_id,qty,amount\n
S014,3,41.85\r\n                             S014,3,41.85\n

# CRLF -> LF: remove carriage returns
tr -d '\r' < in_crlf.csv > out_lf.csv

# LF -> CRLF: add a carriage return at each line end
sed 's/$/\r/' in_lf.csv > out_crlf.csv

Two honest cautions. tr -d '\r' removes every carriage return, including any embedded in data values. Those are vanishingly rare in clean feeds. But they are a reason the conversion belongs after validation has confirmed the file is the clean shape you expect. And beware transfers that rewrite line endings for you: classic FTP's text ("ASCII") mode altered line endings in flight, invisibly to both ends. Modern practice is to transfer everything in binary mode and do line-ending work as an explicit, logged step in your own pipeline. There, it can be seen, tested, and blamed.

Delimiters and Headers: Where Search-and-Replace Goes to Die

A delimiter change looks like a search-and-replace and must never be one, because sooner or later a value contains the new delimiter. One real row is worth a thousand warnings:

source row (pipe-delimited, four fields, no quoting):
S014|Anders, Lena|3|41.85

naive "replace | with ,":
S014,Anders, Lena,3,41.85        <- five fields now; the name has split

correct conversion (comma CSV, quoting the field that needs it):
S014,"Anders, Lena",3,41.85

The rule that follows: delimiter and quoting conversions must be done by something that parses. It reads each row into fields under the source dialect's rules. Then it re-emits the fields under the destination dialect's rules, quoting and escaping as that dialect requires. Every serious scripting environment has a CSV-aware library or cmdlet that does this correctly in a few lines. Bare string replacement is the tool of the incident report.

Header rows are mercifully simpler — add a first line or remove one — with two sharp edges. The first is off-by-one arithmetic. Strip a header from a file that (this once) did not have one and you delete a data row. Add one to a file that already had one and the consumer loads a header as data. The fix is to check before cutting: verify the first line matches the expected header text before stripping, and verify it does not before adding. The second edge is spelling: consumers that demand headers usually demand them exactly. So emit the header from the destination's contract as a literal string — never reconstruct it from guesswork. Trailer records follow the same pattern in reverse, with one sequencing rule. Validation reconciles against the trailer first, and only then does the transform strip it for consumers that cannot digest it.

Mapping Tables: Translating Vocabularies

Systems disagree about values, not just formats: your HR system says department SLS, the payroll provider requires cost center 401. That translation belongs in a mapping table — a small data file the transform step reads — and emphatically not in code as a chain of if-statements:

# deptmap.csv - one line per translation
# source_code,target_code
SLS,401
MKT,402
OPS,410
FIN,455

Configuration-as-data buys you everything code does not. The mapping can be reviewed by the business owner who actually knows whether OPS is 410. The mapping can be changed without touching (or re-testing) the script, diffed in version control, and printed in the runbook. The same principle powers the routing stage later in this series — the routing table is a mapping table whose target column is a destination.

The design decision that matters is the unmapped value policy: what happens when the file contains QAX and the table does not. Fail loudly is the right default — an unmapped code means the world changed and the table has not caught up. Passing unknowns through unchanged, or lumping them into a default bucket, are legitimate documented choices for some flows. Doing either silently is how a new department's payroll vanishes for a quarter. Whatever the policy, the log should count hits per mapping and name every miss.

One family of value transforms deserves its own callout: privacy. Masking account numbers, pseudonymizing customer IDs, and dropping columns the destination has no need for are transforms in exactly this stage. They are done before packaging and sending, while the pipeline can still see the data. The techniques are covered in masking and pseudonymization and data minimization for transfers; the transform stage is where they physically happen.

The Discipline: Small, Testable, Logged

Everything above is mechanics. What makes transforms trustworthy in production is discipline, and it comes down to five habits:

• One transform per step, chained in order. Encoding, then line endings, then delimiters, then columns — each step readable, testable, and replaceable on its own. A single clever script that does everything at once is a single opaque thing that fails as a whole.
• Never transform in place. Read the input, write a new file in the work area, and keep the original until the run is verified end to end. The untouched original is your rollback, your evidence, and your test fixture when something looks wrong three days later.
• Answer the run-twice question. Some transforms are safe to repeat; encoding conversion, run twice, destroys data. Every step should either detect already-transformed input and refuse, or be guaranteed to run exactly once per file. The rerun-safety thinking of the duplicate detection and idempotency series applies to stages, not just whole jobs.
• Keep golden files. A small set of sample inputs with their exact expected outputs, stored next to the transform. After any change — to the script, the mapping table, the tooling — run the samples and diff against expected. Five minutes of harness, and format regressions stop reaching partners.
• Log counts, always. Per file, per step: rows in, rows out, bytes in, bytes out, mapping misses. Most transforms should preserve row counts exactly, so any drift is a red flag. The log makes it visible the same night rather than the partner making it visible next week.

Where Transforms Run, and Who Owns Them

A transform can run at the source, in your pipeline, or at the destination. The best answer is usually: at the side that owns the unusual format. If your legacy system is the one that cannot speak UTF-8, converting on your side keeps the wire format standard and spares every partner the same work. What crosses the wire should be the contract's shape, with each side's local oddities handled privately — and the contract, as ever, written down.

Wherever they run, transforms are your scripts — no transfer tool knows that OPS means 410 in your world. What automation contributes is the frame around them. In Sysax FTP Automation, the script editor with line-by-line debugging is a practical place to develop a transform step and watch it walk through a sample file. The generated task then runs that step in sequence with the rest of the flow. That includes compression, OpenPGP encryption, the transfer, file operations, and the email that reports the outcome. On the receiving side, Sysax Multi Server event triggers (Pro and Enterprise) can launch your convert-on-arrival script as soon as a partner's upload completes. That way, files enter your systems already in house format.

The Version to Tell a Colleague

Transformation is translation: same meaning, different representation, done deliberately in the pipeline instead of accidentally wherever files happen to break. The recurring transforms are a short list — encodings, line endings, delimiters, headers, value mappings. Each has one classic failure: double conversion, mixed endings, naive search-and-replace, off-by-one, the silent unmapped code. Keep every step small and separate, keep the originals, and test against golden files. Count rows in and out, and put vocabularies in mapping tables the business can read. Do that, and the stage most capable of silent damage becomes the most boring part of the flow. That is the highest compliment a pipeline stage can earn.

Next in the series: routing files to destinations applies the same config-not-code idea to deciding where each file goes. The article on validating files before they leave supplies the checks that confirm every transform described here actually produced the contract's shape.

Frequently Asked Questions