I’m building a web application where users need to download their data as CSV files. The problem is making sure these files open correctly in both Excel and Google Sheets regardless of the user’s location.
When I use commas as separators, it works fine for US users but breaks for European users. In places like Germany or France, Excel expects semicolons instead of commas because they use commas for decimal points. So all my data ends up crammed into one column.
If I switch to semicolons, then it’s the opposite problem. US and UK users see everything in one column instead.
I tried adding sep=, at the beginning of the file. This fixes Excel for all regions, but Google Sheets displays it as an actual data row which looks messy.
I also tested putting the separator as a comment like # sep=, or moving it to the second line, but newer Excel versions ignore these approaches.
Another idea was detecting the user’s locale from their browser and generating different CSV formats, but this seems overly complex for what should be a simple export feature.
Is there any standard CSV formatting approach that works consistently in both Excel (across all regional settings) and Google Sheets without showing extra separator rows?
yeah, i get it, it’s a pain! I usually stick with commas too and just let users know about that import wizard in excel. sounds simple but it’s helped a lot. they just gotta follow the steps right.
Try tab-separated values (TSV). I switched after hitting the same problems and it’s way more reliable across regions. Excel and Google Sheets handle tabs the same no matter what locale you’re in - none of those decimal point conflicts that break comma files in Europe. Only catch is you’ll need to escape actual tab characters in your data, but that’s rare. Been doing this for two years and zero user complaints about formatting since I switched.
Just use UTF-8 with BOM and stick to commas. The BOM tells Excel how to handle encoding properly and most regional issues disappear. I’ve been using this for years - works great in both Excel and Sheets without extra rows showing up.
Multiple file formats are a nightmare to maintain. I built something way cleaner.
I set up a workflow that checks the user’s accept-language header or browser locale, then auto-generates the right format. US users get commas, Europeans get semicolons, everyone else gets tabs.
It also handles encoding automatically - UTF-8 with BOM for Excel users, plain UTF-8 for everything else. No more guessing.
I added a preference toggle too. Power users can override the auto-detection if they want. Everyone else just gets files that work.
I threw in email delivery since some corporate networks block direct downloads. Users get a link to grab their file.
Took about an hour to set up. Haven’t touched it since - zero support tickets about broken exports.
Had this exact headache with a financial reporting tool. Switched to pipe delimiters (|) instead of commas or semicolons - never seen a regional setting that treats pipes as decimal separators, so Excel handles them the same everywhere. Google Sheets opens them perfectly too. Only catch is you’ve got to use .txt instead of .csv, but most users don’t care. Been doing this for three years with users in 12+ countries and support requests basically disappeared. Way simpler than trying to detect locales or juggle multiple formats.
TSV (tab-separated values) might be your best bet. I hit this exact problem two years ago building an export feature for our analytics dashboard. Tried a bunch of approaches, but switching to tabs as delimiters completely solved the regional Excel mess. Both Excel and Google Sheets handle TSV files the same way regardless of locale settings - tabs don’t conflict with decimal separators anywhere. Main downside: if your data has actual tab characters, you’ll need to escape or replace them first. Use .tsv extension instead of .csv, though most apps open it fine either way. It’s not technically CSV anymore, but kills the separator guessing game entirely.
I’ve hit this same problem tons of times with export features. Manual import wizards work but they’re a pain for users.
I automated the whole thing instead. The system detects the user’s locale, generates the right CSV format automatically, and handles email delivery. No guessing, no hoops to jump through.
You can set different rules by region - US users get comma-separated files, Europeans get semicolons. No more “my data’s stuck in one column” tickets.
I threw in a preference setting so users can override the auto-detection if they want. Takes 30 minutes to build, saves hours later.
This killed about 90% of our CSV support requests. Users just get files that work with their setup immediately.