PTC
Sign Up Upgrade to Pro Your Account

How to Prepare a CSV File for Translation

A CSV that translates cleanly has stable keys, clear source-text columns, and correct UTF-8 encoding. This guide covers each preparation step. Once your CSV is ready, you can translate it in 3 steps on PTC (Private Translation Cloud). For the standalone CSV service overview, see translate CSV files online with AI.

CSV is the lowest-common-denominator translation format. Every spreadsheet tool, every e-commerce platform, every analytics export speaks CSV. That flexibility cuts both ways. A CSV that "looks fine" in Excel may be impossible to translate cleanly because of column ambiguity, missing keys, or encoding issues. This guide covers what makes a CSV translation-ready.

What a translation-ready CSV looks like

A translation-ready CSV is a CSV where:

  • Every translatable string has a stable, unique key.
  • The source-language column is clearly identified.
  • Target-language columns either already exist (one column per language) or one CSV per target language is produced.
  • Identifier columns (SKU, ID, image URL, price) are obviously non-translatable.
  • The file is valid UTF-8 with no encoding ambiguity.

Two common shapes:

Wide format (one column per language):

key,en,es,fr,de
welcome,Welcome,,,,
cart.empty,Your cart is empty,,,,
cart.items_one,"You have %{count} item",,,,

Long format (one row per string, one file per language):

key,source
welcome,Welcome
cart.empty,Your cart is empty
cart.items_one,"You have %{count} item"

Both work. PTC reads either. Pick whichever fits your existing pipeline.

Choose columns that translators and PTC can read

Make the first row the header. Use plain, descriptive column names like key, source_text, and context. Your translation tool and any human reviewers then know exactly what each column contains.

Required columns. Every translation-ready CSV needs at minimum a key column and a source text column.

  • The key column holds a unique identifier for each string. Your application uses it to match the right translation at runtime. Use a consistent naming convention - lowercase letters, underscores, and dots only. Common formats: button.submit, error.invalid_login, onboarding.welcome_message. Never change a key after translation has started. Changing a key creates a new string and orphans the existing translation.
  • The source text column contains the original text in your base language.

Optional but useful: a context column. Short strings like "Save" or "Back" can mean different things depending on where they appear in your interface. A brief note tells the translator (or AI) what the string is for:

key,source_text,context
button.save,Save,Document editor toolbar
checkout.button.save,Save,Checkout address form

For large files, a namespace or group column helps you organize strings by feature or section.

Language columns. If your workflow keeps source and target in the same file, add a column per target language using BCP-47 codes as the header. Use es_ES for Spanish (Spain), fr_FR for French (France), pt_BR for Portuguese (Brazil).

Use dotted keys for hierarchy. cart.items.one and cart.items.other group naturally. Avoid spaces in keys.

Keep identifier columns separate. If your CSV is a product catalog, the sku, handle, price, and image_url columns should be obviously non-translatable by their names alone. Do not bury them in a generic metadata column.

One CSV per logical scope. Do not mix admin-UI strings with marketing copy in the same file. Different scopes have different tones. PTC's brand voice works better when each project covers a coherent set of strings.

Mark placeholders so PTC preserves them across languages

Placeholders are tokens inside a string that get substituted at runtime. %s, %d, {name}, {{count}}, %{currency}. PTC preserves placeholders verbatim and never translates the token itself.

key,source_text
greeting,"Hello, %{name}!"
cart.total,"Total: %{currency}%{amount}"
notification,"You have %{count} new messages"

Conventions PTC recognises out of the box:

  • %s, %d, %f, %1$s, %2$d - printf style (gettext, Rails, Java).
  • {name}, {count}, {0} - Python str.format, .NET, ICU index syntax.
  • {{name}}, {{count}} - i18next, Mustache, Handlebars.
  • %{name}, %{count} - Rails I18n, Ruby.

If your source text includes HTML tags (<strong>, <br>, <a href="">), treat them the same way as placeholders. PTC detects and preserves them across all formats, in the correct position in each target language. Never translate the tag itself.

If you use a custom convention (e.g., [[token]]), declare it in the PTC project glossary and PTC respects it across every row.

Encode plural forms as separate rows so every language gets the right forms

Most languages have more than two plural forms. Many have rules that no English speaker would predict. Polish has one / few / many / other. Arabic has six forms. Japanese has one (no plural distinction). A translation tool that only stores singular and plural will produce incorrect output for these languages.

Encode plurals as separate rows with a suffix convention:

key,source_text,context
cart.items.zero,Your cart is empty,Zero state
cart.items.one,"You have {count} item in your cart.",Singular: count = 1
cart.items.other,"You have {count} items in your cart.",Plural: count > 1

PTC reads the _one, _other, _zero, _few, _many suffixes (matching the i18next convention) and generates the right plural categories for each target language.

Do not combine plural forms into a single string with slashes or parentheses, like "You have {count} item(s) in your cart.". That approach is not translatable and breaks the grammar of most languages.

Escape special characters so the CSV parser does not break rows

CSV escaping rules (RFC 4180):

  • Commas inside a field: wrap the field in double quotes, otherwise the parser splits your string across cells.
key,source_text
intro,"Welcome to our store, where every product matters"
  • Literal double quotes: escape by doubling them.
key,source_text
quote,"He said ""hello"" and waved"
  • Newlines inside a field: OK if the field is quoted.
key,source_text
address,"123 Main Street
Springfield, IL 62701"
  • Curly quotes: always use straight quotes (") rather than curly quotes (" "). CSV parsers do not recognize curly quotes and will throw formatting errors.
  • Trailing spaces: remove them from all cells before sending. They are invisible in most spreadsheet editors but cause string-matching failures in your application.

For HTML or markup inside a cell, quote the field and let PTC translate only the text:

key,source_text
disclaimer,"<p>By signing up, you agree to our <a href=""/terms"">terms</a>.</p>"

PTC parses the HTML, translates only the human-readable text, and preserves every tag, attribute, and URL.

Save as UTF-8 to avoid corrupted accented characters

Encoding is the single most common cause of CSV translation problems. Without UTF-8, characters outside the basic Latin alphabet may display as ???, empty boxes, or garbled text in your application.

  • Save your CSV as UTF-8 (with or without BOM). Most modern tools default to this. Excel on Windows historically did not.
  • Do not use Windows-1252, ISO-8859-1, or "ANSI" encoding. Accented characters in target languages will be corrupted.
  • If unsure, open the CSV in a text editor like VS Code or Notepad++ and check the encoding indicator. Re-save as UTF-8 if needed.

Excel-specific guidance:

  • Excel for Mac defaults to UTF-8. No action needed.
  • Excel for Windows: choose File > Save As and select CSV UTF-8 (Comma delimited) (*.csv) from the format dropdown. Not plain "CSV (Comma delimited)".
  • Google Sheets: File > Download > Comma-separated values (.csv). UTF-8 by default.

For source CSVs already in the wrong encoding, convert with iconv:

iconv -f WINDOWS-1252 -t UTF-8 source.csv > source-utf8.csv

A worked example: a complete e-commerce CSV that PTC translates without prep

A complete, well-prepared CSV for an e-commerce site:

key,source_text,context
header.welcome,"Welcome to {{shopName}}",Top of page
header.cart.zero,"Your cart is empty",Cart icon - zero state
header.cart.one,"{count} item in cart",Cart icon - singular
header.cart.other,"{count} items in cart",Cart icon - plural
product.add_to_cart,"Add to Cart",Product page CTA
product.out_of_stock,"Out of Stock",Product availability badge
product.price,"{currency}{amount}",Price display
checkout.shipping,"Shipping address",Checkout step heading
checkout.payment,"Payment method",Checkout step heading
checkout.confirm,"Place Order",Final checkout button
errors.email.required,"Email is required",Form validation
errors.email.invalid,"Please enter a valid email address",Form validation
errors.card.declined,"Your card was declined. Please try a different payment method.",Checkout error
promo.tagline,"Sale ends Sunday, grab your deal!",Promo banner - contains comma
tooltip.save,"Click ""Save"" to keep your changes.",Tooltip - contains quotes
disclaimer,"<p>By placing this order, you agree to our <a href=""/terms"">terms and conditions</a>.</p>",Footer legal

This file:

  • Has clear key, source_text, and context columns.
  • Uses dotted hierarchical keys.
  • Handles placeholders ({{shopName}}, {count}, {currency}, {amount}).
  • Handles plurals with .zero / .one / .other suffixes.
  • Escapes HTML and quotes correctly.
  • Is saved as UTF-8.

PTC translates this directly. No prep beyond what is shown.

CSV translation best practices in one list

  • Use a dedicated translation tool. Avoid translating CSV files manually in a text editor or spreadsheet. Dedicated tools like PTC preserve your file structure, handle placeholders correctly, and keep your keys intact.
  • Use standard commas as your delimiter. Some tools default to semicolons. Commas are the most widely supported and least likely to cause import errors.
  • Keep one string per row. Combining multiple strings into a single row makes translations unmanageable and breaks most translation tools.
  • Isolate translatable columns. Keep columns that need translation separate from columns that should remain unchanged (numeric IDs, SKUs, technical parameters). This reduces the risk of a tool or translator accidentally modifying data that should stay untouched.
  • Avoid merged cells and spreadsheet formatting. CSV is plain text. Any formatting applied through spreadsheet software gets stripped when the file is processed.
  • Remove unnecessary columns and metadata. Only include columns your translation tool needs.
  • Test with a small sample first. Before running a large file through translation, test with a small sample to catch structural or encoding issues early.
  • Validate your file before uploading. Check that every row has a unique key with no duplicates, no keys contain spaces or special characters, and there are no broken rows.
  • Keep a backup. Always keep a copy of your original CSV before sending it for translation.

Visual translation review of the rendered catalog or app

Once PTC translates the CSV, the strings render in whatever consumes the CSV. Your storefront, your app, your dashboard. A translated product title that fits the file may overflow a card layout. A French checkout button may use the wrong word for "Place Order". A long German address may break a fixed-width column in a printed invoice.

PTC's Visual AI Review catches these issues. For storefronts and dashboards (browser-based), install the PTC browser extension and record a walkthrough of your store. PTC replays it in every target language and fixes layout and context issues in the CSV automatically. For native apps that consume the CSV, upload screenshots of the relevant screens instead.

Translate product launch announcements and supplier emails

Product launch announcements and customer-facing emails about catalog updates live outside the CSV. PTC's Paste to Translate handles that copy in the same project, using the same glossary and brand voice as your catalog translations.

Translate bulk imports as data arrives with the PTC API

Bulk catalog imports that need translation on arrival can flow through the PTC REST API with Bearer-token authentication, using the same glossary and brand voice as your CSV translations.

Now translate your CSV with PTC

Once your CSV is prepared, translate it in 3 steps:

  1. Upload your CSV and pick target languages.
  2. PTC translates while preserving every column, key, and placeholder.
  3. Download the translated CSVs and import them where they belong.

Start your free 30-day trial - 20,000 words into 2 languages, no credit card.