CSV Helper, Read File With Double Quotes in Field Data

Question

I need some help ion CSV parsing with CSV Helper in C#

my example file is

"SKU","Title","URL","BP","SP","NumberOf","Wid1","Wid2","Wid3"
"Big Corp","CRM updates","test","0","0","0","0","0","0"
"Big Corp 1","CRM "test" updates","test","0","0","0","0","0","0"

my configuration is

using (TextReader reader = File.OpenText(location))
            {
                using (var csv = new CsvReader(reader, System.Globalization.CultureInfo.CurrentCulture))
                {
                    csv.Configuration.RegisterClassMap<ProductMap>();
                    List<Product> records = csv.GetRecords<Product>().ToList();
                    return records;
                }
            }

I am getting error in last row where 1 field has double quote in it. What settings I need to do If I want to escape/remove the double quotes?

I have also tried to replace the double quotes in mapping file but double quotes are being show in wrong place. double quotes are on "test" word but in the data they are shows on "updates" word.

and then

I can replace double quote with an empty string in the mapping file but is there any other solution?

It's not valid CSV. Every `"` inside a field should be double-quoted `""` — Charlieface, Feb 25 '21 at 00:17
Does this answer your question? [Properly escape a double quote in CSV](https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv) — Charlieface, Feb 25 '21 at 00:18
I think I should go ahead with the string replace method as this is not a valid CSV and I have not control over generating the csv. — Ali, Feb 25 '21 at 00:42
@charlieface that answer is for php, I can't use that library. — Ali, Feb 25 '21 at 01:33
The point of that post is how `"` must be escaped, not exactly which library you use to do so. It is going to be difficult for you to work out what's going on, as the CSV is invalid, and your only way out is to check if the quote is preceded or followed by a `,` and if not double it up. This can be very fragile as there may be actual text in one of the columns which has such a feature. You need to sort out whatever is generating the CSV — Charlieface, Feb 25 '21 at 01:37

David Specht · Answer 1 · 2023-01-06T20:26:40.687

You can do it with CsvMode.NoEscape, which will leave the quotes on the end of each field. (It will also break if any field contains a line break). You can then use custom converters to trim the outer quotes off each field. I just wish there was a config like PrepareHeaderForMatch that could be used to process the fields before they are mapped.

void Main()
{
    var config = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        PrepareHeaderForMatch = args => args.Header.Trim('\"'),
        Mode=CsvMode.NoEscape
    };
    
    var data = new StringBuilder();
    data.AppendLine("\"SKU\",\"Title\",\"URL\",\"BP\",\"SP\",\"NumberOf\",\"Wid1\",\"Wid2\",\"Wid3\"");
    data.AppendLine("\"Big Corp\",\"CRM updates\",\"test\",\"0\",\"0\",\"0\",\"0\",\"0\",\"0\"");
    data.AppendLine("\"Big Corp 1\",\"CRM \"test\" updates\",\"test\",\"0\",\"0\",\"0\",\"0\",\"0\",\"0\"");

    using (var reader = new StringReader(data.ToString()))
    using (var csv = new CsvReader(reader, config))
    {
        csv.Context.TypeConverterCache.AddConverter<int>(new IntTrim());
        csv.Context.TypeConverterCache.AddConverter<string>(new StringTrim());
        csv.GetRecords<Product>().ToList().Dump();
    }
}

public class StringTrim : StringConverter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        return base.ConvertFromString(text?.Trim('"'), row, memberMapData);
    }
}

public class IntTrim : Int32Converter
{
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        return base.ConvertFromString(text?.Trim('"'), row, memberMapData);
    }
}

public class Product
{
    public string SKU { get; set; }
    public string Title { get; set; }
    public string URL { get; set; }
    public int BP { get; set; }
    public int SP { get; set; }
    public int NumberOf { get; set; }
    public int Wid1 { get; set; }
    public int Wid2 { get; set; }
    public int Wid3 { get; set; }
}

CSV Helper, Read File With Double Quotes in Field Data

1 Answers1