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.


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?
  • 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 "<option> (recommended)" or similar and just press the recommended option. – Erik Aug 17 '15 at 16:57

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.

  • 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 8 '15 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.