Programmatically generated CSV file format issue for Excel and Numbers

Question

In my iOS project, I have programmatically generated csv file from my data. Most of time, it looks all good for Microsoft Excel and Apple Numbers to open with.

But when the cell data is something like 5 - 60, it seems Excel would automatically convert it to date value like May-60, while Numbers open it correctly.

I have found this thread: https://stackoverflow.com/a/165052/833885, so the solution makes Excel happy is using "=""5 - 60""". But this will make Numbers shows ="5 - 60"......

You can quickly generate empty csv file to test what I described above.

Is is possible to generate csv file that makes all world happy???

Thanks in advance.

Try this `'5 - 60`. This will make excel read it as a string. Although I haven't tested this — Zac, Jul 26 '17 at 08:29
I'm presuming that's no good to you? could you not read that when you parse the file? — Zac, Jul 26 '17 at 08:40
@Zac What I mean is in your method, there is additional ' as a prefix, I don't want it if it possible. — brianLikeApple, Jul 26 '17 at 08:53

score 1 · Accepted Answer · answered Jul 26 '17 at 08:31

1

You can create a new file in excel and import from the data ribbon tab - this gives options to specify the data types for 'columns' in the csv. A bit of a pain but will avoid the issue.

answered Jul 26 '17 at 08:31

Lee Cordell

276
3
9

OK, this is working around! I will wait until whether there is better solution. Thanks! – brianLikeApple Jul 26 '17 at 10:43

Programmatically generated CSV file format issue for Excel and Numbers

1 Answers1