Which delimiter allows major spreadsheet programs to open CSV files by double-clicking?

Question

I'm writing an iOS 8 app that allows you to export data to be sent as an email attachment. Currently, I'm exporting the data to a CSV file with 7 columns and x number of rows. When I first created it, I used a comma for delimiter, but that wasn't automatically detected by Excel - so I had to go to data-import as so on to read it correctly. I later changed it to a semicolon, and now it reads correctly in Excel as well by simply double-clicking the file, but I'm not sure if this is the optimal delimiter.

What I need to know is what delimiter I should use that can be read by the majority of spreadsheet readers (including Excel) simply by double-clicking the file.

"it reads correctly in Excel as well by simply double-clicking the file" -- using an English version of Excel and/or English regional settings...? (See also this Excel-only trick.) — Arjan, Aug 17 '15 at 11:29
True, @Raystafarian, but that's not double-clicking. (I know there is no definitive solution...) — Arjan, Aug 17 '15 at 11:39
@Arjan Yeah, but that's basically the point of that entire question. Actually, it's the point of most of the tag's questions — Raystafarian, Aug 17 '15 at 11:41
This answer compares comma, semicolon and tab as separator. Maybe it helps — nixda, Aug 17 '15 at 13:22
There are a multitude of readers and versions. I suspect the only way to answer the question in bold is to identify the ones you consider relevant and test them. As written, the question is too broad. Can you focus it better? — fixer1234, Aug 18 '15 at 02:11
@fixer1234 I expect MS Excel to be used the most, and perhaps OpenOffice second. But I have no way of confirming version number. It's a globally available app. — Erik, Aug 18 '15 at 12:08

score 2 · Answer 1 · answered Aug 17 '15 at 11:45

2

What delimiter should I use that can be read by a majority of spreadsheet readers?

Short Answer:

There is no general solution to this problem.

Long Answer:

You haven't defined majority.

There is no real answer to this question as it depends on how the email recipient has configured their computer's regional settings and excel settings.

If you can answer all of the following questions then only you can decide what the appropriate delimiter should be:

Who is going to use your app and what country are they in?
Who are the users of your app going to email?
What countries are the email recipients in?
How do they have their regional settings configured?
How do they have their spreadsheet configured?
Can their computer even open a speadsheet by double clicking?

answered Aug 17 '15 at 11:45

DavidPostill

156,873

I see. This is a new feature and will come with an update to my app which is already in the App Store. The app is currently available worldwide, and I have no way of telling whether the user can open it by double-clicking, or how their settings are configured. The closest thing I can get to this would be to limit the app distribution to some countries. – Erik Aug 17 '15 at 14:49
Or, @Erik, default to something and allow the users to specify something else? (Next problem: choose a date format, if applicable...) – Arjan Aug 17 '15 at 16:54
@Arjan that's definitely an idea.I thought of that as well but what should I default to? - in your opinion. Many users would see, say a UIActionSheet, with an option saying " – Erik Aug 17 '15 at 16:57

score 0 · Answer 2 · answered Aug 17 '15 at 13:11

0

You just have to add the line 'sep=,' (or 'sep=;' if you want to use ;) at the top of your csv document. Excel will use then the defined character as separator.

answered Aug 17 '15 at 13:11

Fabrice Aneca

1

1

Beware though: as noted in comments elsewhere, that is an Excel-only trick (and might mess up in other software). And when saving the opened CSV file in Excel, it removes that line and uses the default separator again. – Arjan Aug 17 '15 at 13:29
I tried this, but it turned out to be a non-viable solution as Arjan said – Erik Aug 17 '15 at 14:47
yup – Raystafarian Aug 17 '15 at 16:55
Also note that apparently, "For this to work in Excel 2013, you must uncheck Use system separators in Options>Advanced". – Arjan Sep 08 '15 at 10:21

Which delimiter allows major spreadsheet programs to open CSV files by double-clicking?

2 Answers2

What delimiter should I use that can be read by a majority of spreadsheet readers?