How to Parser CSV Data When a Field Has Commas in C#

However, when a field itself contains commas, it can create a conflict with the delimiter, making it difficult to identify which data belongs to which field. The key to solving this issue lies in whether the field with commas is enclosed in quotes or not.

If the field with the commas is enclosed in quotes, most modern spreadsheet programs and CSV generators will automatically handle this situation.

For example, a CSV file might look like this:

Title,Year
"Crazy, Rich Asians",2018
Inception,2010

Here, the field "Crazy, Rich Asians" is correctly enclosed in quotes, and the CSV parser can handle it without issue. Instead of manually parsing the CSV, it is best to use a dedicated library, such as CsvHelper, to handle quote-enclosed commas.

To use CsvHelper, you first need to install the package:

Install-Package CsvHelper

Once the package is installed, you can parse the CSV data as shown below:

using CsvHelper;
using System.Globalization;
using System.IO;

using (var reader = new StreamReader(@"C:\sample.csv"))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    foreach (var movie in csv.GetRecords<Movie>())
    {
        Console.WriteLine($"{movie.Title} was released in {movie.Year}");
    }
}

This code will correctly handle the CSV file and output:

Crazy, Rich Asians was released in 2018
Inception was released in 2010

This creates a problem because the parser will mistakenly treat "Crazy" as one field, " Rich Asians" as another, and 2018 as the third. This is malformed CSV data since it doesn’t follow the standard of quote-enclosing fields with commas.

If you control the generation of the CSV data, it’s best to fix the problem by enclosing the fields with commas in quotes. However, if you can’t change the data and must process it as-is, you can still handle it by merging the parts of the fields that should belong together.

The strategy is to:

1. Split each line by commas into an array.
2. If the number of fields exceeds the expected count, merge the parts that belong to the string field.
3. Remove the redundant parts and insert the merged string into the correct position.

using System.IO;
using System.Linq;

var csvFilePath = @"C:\sample.csv";
var expectedFieldCount = 2;

foreach (var line in File.ReadLines(csvFilePath).Skip(1)) // Skip the header row
{
    var fields = line.Split(',').ToList();

    // Check if the string field had commas and merge its parts
    if (fields.Count > expectedFieldCount)
    {
        int mergeCount = 1 + (fields.Count - expectedFieldCount);  // Merge all extra parts
        var mergedString = string.Join(",", fields.Take(mergeCount));  // Join the string parts
        fields.RemoveRange(0, mergeCount);  // Remove the parts that are merged
        fields.Insert(0, mergedString);  // Insert the merged string into the list
    }

    Console.WriteLine($"{fields[0]} was released in {fields[1]}");
}

Output:

Crazy, Rich Asians was released in 2018
Inception was released in 2010

Consider a problematic CSV line like this: "Crazy, Rich Asians,2018".

Here's how the algorithm handles it:

1. Split the line by commas: ["Crazy", " Rich Asians", "2018"]
2. This results in 3 fields, while we expect only 2. This indicates that the "Title" field contains a comma.
3. Merge the first two fields ("Crazy" and " Rich Asians") into "Crazy, Rich Asians".
4. Remove the individual "Crazy" and " Rich Asians" fields and insert the merged string into the list.
5. The final result is ["Crazy, Rich Asians", "2018"], which is now correctly parsed.

In cases where the CSV data is malformed (e.g., fields with commas not enclosed in quotes), you can still parse it by merging fields manually. This method requires recognizing when a field has an extra comma and handling it appropriately. If you have control over the CSV generation, the best practice is to ensure that any field containing commas is properly enclosed in quotes.