Objective-C, correctly separate columns in CSV by comma

Question

I have an Excel file that I exported as a CSV file.

Some of the data in the columns also have commas. CSV escapes these by putting the string in the column in quotes (""). However, when I try to parse it in Objective-C, the comma inside the string seperates the data, as if it were a new column.

Here's what I have:

self.csvData = [NSString stringWithContentsOfURL:file encoding:NSASCIIStringEncoding error:&error];

//This is what the data looks like:
//"123 Testing (Sesame Street, Testing)",Hello World,Foo,Bar

//Get rows
NSArray *lines = [self.csvData componentsSeparatedByCharactersInSet:[NSCharacterSet newlineCharacterSet]];

//Get columns
NSArray *columns = [[lines objectAtIndex:0] componentsSeparatedByString:@","];

//Show columns
for (NSString *column in columns) {
    NSLog(@"%@", column);
}

//Console shows this:
"123 Testing (Sesame Street
Testing)"
Hello World
Foo
Bar

Notice how "123 Testing (Sesame Street and Testing)" are output as separate columns. I need these to be one. Any ideas?

Answer 1

Any ideas?

Design an algorithm.

At the start of the input you have one of three possibilities:

1. You have a " - parse a quoted field
2. You have a , - handle an empty field
3. Otherwise - parse a non-quoted field

After parsing if there is any more input left then iterate (loop).

You might start with some variables:

NSUInteger position = 0;                // current position
NSUInteger remaining = csvData.length;  // left to parse

then enter your loop:

while(remaining > 0)
{

get the next character:

   unichar nextChar = [csvData characterAtIndex:position];

Now check if that character is a ", , or something else. You can use an if or a switch.

Let's say it's a comma, then you want to find the position of the next comma. The NSString method rangeOfString:options:range will give you that. The last argument to this method is a range specifying in which part of the string to search for the comma. You can construct that range using NSMakeRange and values derived from position and remaining.

Once you have the next comma you need to extract the field, the substringWithRange: method can get you that.

Finally update position and remaining as required and you are ready for the next iteration.

You'll have to handle a few error cases - e.g. opening quote with no closing quote. Overall it is straight forward.

If you start down this path and it doesn't work ask a new question, showing your code and explaining where you got stuck.

HTH