Your app has an "Export to CSV" button. In English it works: the file opens in Excel with tidy columns and correct prices. Then a customer in Germany or Brazil opens the same file and sees every row crammed into column A, or a price of 12.50 shown as a date, or a quantity that is a thousand times too large. When they edit the sheet and import it back, the damage lands in your database. Locale-aware CSV import export is the fix, and it is a different job from translating your UI.
This guide is for teams that already localize screens and maybe already handle decimal input on a localized keyboard. It covers the file boundary instead: which locale a CSV is written in, which delimiter goes with which decimal separator, how dates become ambiguous, what Excel's sep= line does and does not fix, and how to test a round trip.
The short answer
- Give CSV files an explicit interchange locale. Do not let it fall out of the app language or the device region by accident.
- Pair the field delimiter with the decimal separator: comma delimiter with period decimals, semicolon delimiter with comma decimals.
- Format on export and parse on import with the same locale, using real number and date formatters.
- Offer a fixed machine format (period decimals, ISO 8601 dates, UTF-8) for files that only travel between systems.
- Decide deliberately whether to write Excel's
sep=hint, and test it with accented text. - Release-test a round trip on at least one period-decimal and one comma-decimal locale.
Why CSV breaks when it crosses a locale
CSV looks like a standard. RFC 4180 describes the common grammar: records on separate lines, fields separated by commas, and fields that contain commas, quotes, or line breaks wrapped in double quotes. It says nothing about decimal separators, digit grouping, or date order, and it is an informational document rather than a rule spreadsheet tools must follow.
Spreadsheet tools fill that gap with locale. Google's AppSheet documentation on how to configure the CSV locale spells out the convention most tools share. The CSV locale controls how dates, times, decimals, percentages, and prices are written, and it also controls the delimiter between values. When the decimal separator is a period, the delimiter is a comma. When the decimal separator is a comma, the delimiter is a semicolon. For English (United States), AppSheet documents M/d/yyyy dates, h:mm:ss tt times, period decimals, and comma delimiters, and the locale defaults to that setting unless the app author picks another.
The pairing exists for a practical reason. If a German file wrote 12,5 for twelve and a half and also used commas between fields, nobody could tell where a value ends. So the semicolon takes over as the separator, and a tool that expects commas sees one long field per line.
Unicode's CLDR number symbols data is where most formatters get each locale's decimal and grouping symbols. CLDR also notes that regional variants such as English used in Germany can follow either the language's conventions or the country's, which is one more reason not to guess a file's format from a language tag alone.
What the failures look like in practice
Practitioner threads show the same few patterns across tools and years. These are author reports about specific setups, but they line up with the documented conventions.
- An R user with European CSV files that write
0,5for one half cannot get numbers instead of strings. The top answer points toread.csv2, which assumes comma decimals and semicolon separators. - A pandas user reads a semicolon file and later hits a
TypeErrorbecause values such as-0,59arrived as text, in a thread about converting comma decimals within a DataFrame. - A SAS user importing a semicolon file with comma decimals reports that 350,58 became 35,058, and that re-exporting to German Excel then showed 35.058,00. The value grew by a factor of one hundred with no error.
- A Power BI user lost every decimal separator importing a period-decimal file on a machine set to the Dutch region. The top answer changes the report's own locale setting, which is stored per report.
Each case is a file written in one convention and read in another.
Pick the CSV locale on purpose
The first decision is which locale a given file uses. Most apps never make it explicitly. The export code calls a default formatter, which follows the device or the server, and the import code assumes whatever the developer's machine produced.
The people opening your file do not care about your app language. A GitHub pull request that makes a library's CSV export and import locale-aware describes the mechanism directly: Excel reads CSV numbers using the operating system locale's decimal separator, so on pt-BR or de-DE machines the old always-comma, period-decimal export was misread. Power BI uses the report's locale. AppSheet uses the CSV locale configured on the import or export action, and it lets the author supply a locale expression per user, for example a CsvLocale field read through USERSETTINGS(CsvLocale).
Make the CSV locale its own setting, separate from the app language and the device region, because Excel, Power BI, and AppSheet read a file with the locale of the machine, report, or action that opens it rather than the language of the app that wrote it. An expat who reads your app in English on a German Windows machine needs a semicolon-delimited, comma-decimal file, because German Excel is what opens it. A Brazilian finance team that sets the app to English for training still opens exports in Portuguese Excel. Tying the CSV format to app language gets both users wrong.
A workable default order:
| Signal | Use it for | Why |
|---|---|---|
| Explicit CSV locale chosen by the user or admin | Every export and import | It names the tool and machine that will read the file |
| Saved per-account or per-workspace value | Default for the picker | Teams share files, so a workspace default beats a personal guess |
| Device region formatting preference | First-run suggestion only | It usually matches the spreadsheet tool on the same machine |
| App UI language | Never the only signal | Language and number conventions often differ for the same person |
Show the choice next to the export button with a preview of one formatted row, such as 12,50;31.12.2026 versus 12.50,12/31/2026. A preview catches the mismatch before the file leaves the app.
Write and read with the same formatter
Once the locale is known, use real formatters on both sides, configured identically. Apple's NumberFormatter converts between numeric values and their textual representations, and DateFormatter does the same for dates. On Android, android.icu.text.NumberFormat formats and parses numbers for a locale; its reference notes that reusing one instance for many numbers avoids refetching locale conventions each time.
The rule that prevents most damage is symmetry. Whatever locale, grouping setting, fraction digits, and date pattern you used to write a column, use the same ones to read it. Telerik's CSV culture recipe shows the shape: set the import culture to match the incoming file (period decimal, comma delimiter), then switch both the culture and the delimiter together when exporting a German file with semicolons.
A sketch of the export side:
struct CSVLocaleProfile {
let locale: Locale
let delimiter: Character // "," or ";"
let datePattern: String // e.g. "dd.MM.yyyy"
}
func makeNumberFormatter(_ p: CSVLocaleProfile) -> NumberFormatter {
let f = NumberFormatter()
f.locale = p.locale
f.numberStyle = .decimal
f.usesGroupingSeparator = false // grouping marks add ambiguity in files
f.maximumFractionDigits = 6
return f
}
func delimiter(for locale: Locale) -> Character {
// Comma decimals need a semicolon delimiter; period decimals keep commas.
return locale.decimalSeparator == "," ? ";" : ","
}
Turning off grouping separators in exported files is a recommendation, not a platform rule. Grouping marks are the most common source of ambiguity on import, because 1.234 is one thousand two hundred thirty-four in German and a little over one in English. A spreadsheet will format the column for display anyway.
Dates need the same care
Dates fail more quietly than numbers. 03/04/2026 is March 4 in a US file and 3 April in a British one, and no parser can tell from the text. AppSheet's documented US format is M/d/yyyy; a German file usually carries dd.MM.yyyy. Write dates with the profile's pattern, read them with the same pattern, and reject a value that does not match instead of letting a lenient parser swap day and month.
Keep a machine format alongside the human format
Not every CSV is opened by a person. Files that move between your servers, a partner's import job, or a data warehouse should use one fixed format regardless of who triggered the export: comma delimiter, period decimals, no grouping, ISO 8601 dates, UTF-8. This is a design recommendation drawn from the failures above. Label the two options plainly in the UI, for example "Spreadsheet (your region)" and "Data exchange (standard)", so integrations never inherit someone's regional settings.
Store values canonically inside the app no matter which file format was used. The CSV locale is a property of the file, not of the data.
Excel's sep= line and its catch
Excel recognizes a first line of the form sep=; or sep=, as an instruction about the delimiter. Teams reach for it because it lets one file open correctly on machines with different regional settings. The locale-aware export in the quiver pull request writes a sep=; header for comma-decimal locales and sep=, for period-decimal ones so Excel picks the right column separator on open.
Treat Excel's sep= hint as a trade-off, not a free fix: practitioners report that a sep= line after a UTF-8 byte order mark can make Excel ignore the mark and garble accented text, so test the hint together with non-ASCII data or offer XLSX instead. A developer generating CSV for German users reports that adding sep=; after a UTF-8 byte order mark made Excel stop honoring the mark, so umlauts turned into garbage again. The top answer, written about Excel 2007, says there was no way to get both encoding and separator right across locales on double-click. Another answer tested Excel 2013 and found a workaround with the hint at the end of the file, at the cost of a stray sep= cell in the last row, and reported that UTF-16LE with tab delimiters opened cleanly. A PowerShell user prepending SEP=, to Export-CSV output hit a different side effect: a later pass read the hint back as a column heading.
These are author reports tied to specific Excel versions, so test the versions your customers use. In practice:
- If names, addresses, or product titles contain non-ASCII characters, check the encoding and the hint in one file before shipping.
- For spreadsheet users, a locale-matched delimiter with no hint is often enough when the CSV locale really matches the reader's machine.
- When users mostly want a spreadsheet rather than a data file, offering XLSX avoids delimiter and decimal guessing entirely.
- Your importer must accept and strip a leading
sep=line, because users will send files that contain one.
Import without guessing
Imports deserve more suspicion than exports, because the file may have passed through someone else's spreadsheet. A safe import order:
- Detect encoding and a leading byte order mark.
- Read and remove a
sep=line if present, and use it as the delimiter. - Otherwise ask for the CSV locale, defaulting to the saved profile, and infer the delimiter only as a hint checked against the header row.
- Split records with a real CSV parser that respects quotes and both CRLF and LF line endings.
- Parse each numeric and date column with the chosen locale profile.
- Show a preview of the first rows with parsed values before committing anything.
Never repair an import with character substitution: split fields with the detected delimiter first, then parse each numeric column with the file locale, because a blind semicolon-to-comma swap spreads comma decimals across columns and a wrong decimal guess silently turns 350,58 into 35,058. The quiver pull request describes the failure. Its old importer replaced every semicolon with a comma before parsing, which split comma decimals across several numeric columns and corrupted re-imported data. The fix parses with the detected delimiter directly and only then normalizes numbers, stripping grouping marks and converting the decimal comma. The SAS report above shows the other half of the risk: a wrong decimal assumption does not raise an error at all.
Add plausibility checks where your domain allows them. A unit price that is one hundred times its previous value, or a date that moved by months in a bulk update, deserves a confirmation step rather than a silent write.
Handle failure and recovery
Expect partial bad files. Reject the import as a whole when the header does not match expected columns or when the column count differs between rows, because that usually means the delimiter is wrong. Report row and column numbers in errors, along with the raw text and the locale you used, so a user can see "expected 12,50, read 1250" instead of a vague failure.
Make imports reversible where data matters: keep the previous values for an import batch so an admin can roll it back. If you support scheduled or API-driven imports, record the CSV locale used for each batch, because the person debugging a corrupted price next month will need to know which convention was assumed.
When a user changes their CSV locale, do not reinterpret files they already imported. The setting applies to the next file.
Verify the solution
Unit tests for formatters are not enough, because the failure appears at the boundary with someone else's tool. A small release matrix covers it:
| Test | Locale profile | Pass condition |
|---|---|---|
| Export and open in Excel | en-US |
Columns split correctly, 12.50 stays a number |
| Export and open in Excel | de-DE or pt-BR |
Semicolon columns split, 12,50 is a number, dates read as day first |
| Export, edit one cell, re-import | Both profiles | No value changes except the edited cell |
Import a file with a sep= line |
Either | Hint removed, delimiter honored, no stray column |
| Import with accented names | Either | Names survive with your chosen encoding and hint policy |
| Machine format export | Any user locale | Output is byte-identical regardless of the user's region |
The quiver change also adjusted its tests: because export output became locale-dependent, byte-level assertions read files through a small canonicalizer that maps a semicolon file back to comma and period form. The same approach works in your own test suite: it keeps one set of expected values while still exercising each locale's output.
Run the round-trip check in Google Sheets as well as Excel. Google's Sheets update on localized formatting and CSV imports says Sheets uses regional decimal separators from the spreadsheet locale and autodetects semicolons when splitting CSV columns, so the two tools may disagree on the same file.
Common mistakes
- Using the app UI language to choose the CSV format. Language and number conventions differ for many users.
- Formatting exports with the device default and parsing imports with a hard-coded US pattern, or the reverse.
- Writing grouping separators into exported numbers.
- Adding
sep=without testing accented text in the same file. - Fixing imports with find-and-replace on separators.
- Letting integrations consume the regional spreadsheet format instead of a fixed data-exchange format.
- Testing only in English Excel on a US-configured machine.
What to do next
List every place your product writes or reads CSV: user exports, admin imports, scheduled reports, and partner feeds. For each one, write down which locale it uses today, whether that was a decision or an accident, and who opens the file. Add an explicit CSV locale setting with a preview, a fixed data-exchange option, and the round-trip matrix above to your release checks. If you already keep translated strings for export dialogs and column headers in your localization workflow, the iOS and Android guides cover where those strings live; the file format itself needs the separate decision this guide describes.
