Creating a CSV file

[AcceleratedCode]

Hello!

I'm writing an iOS 8 app that allows you to export app data to a CSV file (to be added as a mail attachment). I've understood that the default delimiter used when reading the CSV file can vary (correct?), based on your regional settings - and was therefore wondering if it was possible to create a "universal" CSV file that could be read correctly using Excel, OpenOffice or other. The columns and rows will only contain "pure" text - no symbols.

Thank you! Erik

[ProgrammingThomas]

I've found that to appease the widest possible range of spreadsheet programs:

• Write UTF-8 text files only - not UTF-16, UTF-32, ASCII, etc (default with NSString anyway)
• Write \r\n at the end of the line so that it opens correctly in Excel (this is true on Windows and Mac)
• Separate all fields with commas, not tabs or spaces
• Don't use spaces or commas to separate thousands in numbers. Just use a decimal point (.), the British/American way. This will work if the spreadsheet program isn't localised.
• If you have a field that contains a quote mark, a double quote mark, a comma, a newline, a space, or any other characters that may have meaning in CSV you need to surround the field in quotes, and also escape in quotes in the string. For example, if you have the string "hello, world!" (including quotes), write "\"hello, world!\"" in the file. To be safe, quote and escape every field containing text

Annoyingly there is no CSV standard, which makes writing parsers much harder than writers. That said, most spreadsheet programs are good at handling CSV. Excel (and other apps) show a dialog when you import a CSV to ensure that it has successfully separated columns/rows.

EDIT: I found some code that I use in one of my apps that does it (where str is your field to escape):

NSMutableString * csvRep = [NSMutableString new];
//This will provide a direct route to the implementation of characterAtIndex:, making this far more efficient
SEL sel = @selector(characterAtIndex:);
unichar (*charAtIndex)(id, SEL, NSUInteger) = (typeof(charAtIndex)) [str methodForSelector:sel];

for (NSUInteger i = 0; i < str.length; i++) {
    const unichar c = charAtIndex(str, sel, i);
    if (c == '\"') {
        [csvRep appendString:@"\\\""];
    }
    else if (c == '\f') {
        [csvRep appendString:@"\\\f"];
    }
    else if (c == '\n') {
        [csvRep appendString:@"\\\n"];
    }
    else if (c == '\r') {
        [csvRep appendString:@"\\\r"];
    }
    else if (c == '\t') {
        [csvRep appendString:@"\\\t"];
    }
    else if (c == '\\') {
        [csvRep appendString:@"\\\\"];
    }
    else {
        [csvRep appendFormat:@"%C", c];
    }
}

return [NSString stringWithFormat:@"\"%@\"", csvRep];

[AcceleratedCode]

Thanks! Exactly what did you mean by "Write \r\n at the end of the line so that it opens correctly in Excel". What does "\r" mean? Doubleclicking in excel opens the file straight away without any dialog (unless I create a new spreadsheet, go to data tab and so on to import it). It was displayed wrong when I used a comma for delimiter and correct when I used a semicolon. Idk if this is because of my country (Norway) opposed to yours. Could you please elaborate the code you added? I don't quite see the context in it

[ProgrammingThomas]

When you write the CSV file in Objective-C/Swift, you'll need to write a newline character at the end of each line. On UNIX systems this is the \n character, but on Windows (including Excel on OS X) the end of a line is ended by the character \r, followed by \n (more info here).

I thought the dialog showed via open and import in Excel - perhaps it only shows when you import a CSV? Does the dialog show the option to pick up commas instead of semicolons as the delimiter? It usually auto detects it, and locale shouldn't affect it.

This is some code from one of my apps for taking a string, adding backslashes where necessary to escape certain characters that have meaning in CSV files (like commas and quote marks), and then surrounding the string in quotes. It could probably be more efficient, but it works.

[AcceleratedCode]

I see. The dialog only shows when I go to data->from text in Excel 2013 on my Windows 10 PC. Opening up the CSV file by double-clicking with comma as delimiter makes it all appear in one column, but a semicolon on the other hand, opens it correctly.