CSV and TSV translation files

The shape that works

One row per string, a source column, a target column, and as many other columns as you like for identifiers, comments and status.

ID,Source,Target,Comment
1,"Save your work before you close the editor.","Speichern Sie Ihre Arbeit, bevor Sie den Editor schließen.",
2,"Deleted {0} files and {1} folders.","{0} Dateien und {1} Ordner gelöscht.",Placeholder order may differ
3,"The upload failed: %s","Der Upload ist fehlgeschlagen: %s","%s carries the raw server error"

Rows are addressed by their position among the data rows, and an export matches segments back by that identifier rather than by walking two lists in parallel. A row with no matching segment is copied through untouched instead of being shifted up.

Encoding comes first

Nothing else can be detected until the bytes are text. A byte-order mark settles it: UTF-8, UTF-16 little-endian or UTF-16 big-endian. Without one, a UTF-16 file is recognized by the null bytes interleaved through its first characters, and everything else is treated as UTF-8.

Decoding is strict. A file whose bytes do not match its detected encoding fails when you open it, rather than decoding into replacement characters and producing quietly damaged output later. If you see that error, the file is almost certainly saved in a legacy code page such as Windows-1252 — re-save it as UTF-8 and open it again. Whatever mark arrived is written back on export, which matters because Excel on Windows decides how to read a CSV partly from it.

Delimiter detection

Four delimiters are considered: comma, semicolon, tab and pipe. A preview-based guess runs first over the first twenty rows. Guessers refuse to commit when there is not enough evidence — a short file can average fewer than two fields per row on every candidate — and the usual failure mode there is a silent fallback to comma, which turns a semicolon file into one column and loses the job.

So when the guess declines, a deterministic scoring pass takes over: each candidate is measured by its average field count across the preview, highest wins, ties break towards the most regular row lengths. Nothing depends on timing or randomness, which is why re-opening a file always yields the same answer and why an export after a browser restart writes the delimiter it read. If detection still ends up with one column, that is an error naming the delimiter it found rather than a file with no target.

The semicolon dialect

On locales where the decimal separator is a comma — German, French, Spanish, Russian and most of continental Europe — Excel's "Save as CSV" writes semicolons instead of commas, because the comma is busy inside numbers. It also expects semicolons when reading. A file exported from a German copy of Excel looks like this:

Quelle;Ziel
Save changes;Änderungen speichern
"Choose a language; the list is sorted alphabetically.";"Wählen Sie eine Sprache; die Liste ist alphabetisch sortiert."
Deleted {0} files.;{0} Dateien gelöscht.

Two things are going on. The delimiter is a semicolon and the header names are German — both are handled, because the header vocabulary covers English, German, French, Spanish, Russian and Chinese. Note the third row: a field that itself contains a semicolon is quoted, so the delimiter inside it is data rather than a column break.

The important half is the export. The semicolon goes back in, so the delivered file opens in the same copy of Excel with the same columns. Converting it to a comma file would be technically correct and practically useless to whoever asked for it.

Quoting and embedded newlines

Exporters are usually all-or-nothing about quotes: either every field is wrapped or only the ones that need it. That distinction is invisible after parsing, so it is measured on the raw text first — if every sampled field was quoted, the export quotes everything. A doubled quote inside a quoted field is the escape for a literal quote character, and it round-trips as one.

A quoted field may contain line breaks, and multi-line interface strings do:

ID,Source,Target
1,"Line one
line two","Zeile eins
Zeile zwei"

That is one data row, not three. It is read as one segment and re-quoted on export, so the row stays one row. A newline you type into a target survives the same way — legal, and worth checking against whatever consumes the file, because plenty of importers still split on line breaks before they parse quotes. Line endings are measured across the whole file and the dominant one is written back, and a trailing newline is preserved if it was there and not invented if it was not.

Header and column detection

A first row counts as a header when it names both a source and a target column, or when at least two of its cells are recognizable column names — the vocabulary includes identifier, comment and status names as well as source and target, in all six languages. Anything weaker would misread a data row whose first cell happens to be the word "Source". Anything stronger would miss Quelle;Ziel. Names are matched after normalization, so Target (de-DE) and Source_Text both resolve, and the language tag in the brackets becomes the document's target language.

When the headers do not resolve both columns, the rest are picked positionally by ranking every column: prose first, then short text, then numeric, then wholly empty. The last rank is the one that matters. An untranslated file has an empty target column, so empty columns have to stay selectable — ranking them last without excluding them is what lets a source-only file open at all. Two failures are reported rather than guessed around: no plausible pair of columns, and a source and target that resolve to the same one. All of it can be overridden before you start work.

What "preserves the dialect" means concretely

CSV cannot be byte-spliced the way an XML format can: one new character in a field can change whether that field needs quoting, so an export re-emits the whole file. Fidelity therefore means putting back exactly what was detected. An exported file has:

• The same delimiter, including tab for a TSV.
• The same quote policy, quote character and escape character.
• The same line ending, and the same presence or absence of a trailing newline.
• The same encoding and the same byte-order mark.
• Every column, including the ones that were never mapped — identifiers, comments, status, context, anything.
• Every row, in its original order, including the header row.

The only cells that change are targets you edited, and all of it is re-derived from the original bytes at export time rather than remembered, so an export after a browser restart matches one made in the original session.

One honest limitation: unlike the XML formats, a CSV export is not guaranteed byte-identical when you change nothing. In a file that is not uniformly quoted, fields the producing tool quoted unnecessarily may come back unquoted. The data, the column count and the row order are unchanged.

Tab-separated files use the same machinery — tab is one of the four candidates. They are easier to get right than comma files, because prose rarely contains a tab, and harder to inspect, because a tab and a run of spaces look identical in most editors. If you get to choose, tab-separated UTF-8 is the least trouble.

Related formats

XLSX uses the same column-detection vocabulary, with styles and multiple sheets to worry about instead of delimiters. If the content started life as XLIFF, a spreadsheet round trip gives up its states, inline tags and comments.

Going the other way: convert an XLIFF file to a spreadsheet.