Search code examples
c#csvhelper

CsvHelper working differently with TextDelimiter & FieldDelimiter

We are using CsvHelper for processing csv & dat files. For the dat files processing, we are keeping the "TextDelimiter": "þ" & "FieldDelimiter": "¶"

If the dat file is NOT having any double quotes ('"'), then the csvhelper is working fine. If the dat file is having a double quotes ('"'), then the csvhelper is splitting the data incorrectly.

using (var reader = new StreamReader(datFilePath))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    if (LoadFileExtn.ToLower() == ".dat")
    {
        //csv.Configuration.IgnoreQuotes = true;
        csv.Configuration.Delimiter = "¶";
        csv.Configuration.Quote = "þ";
    }
    else
    {
        csv.Configuration.Delimiter = "|";
    }
    csv.Read();
    csv.ReadHeader();
    List<IDictionary<string, object>> dataRecords = csv.GetRecords<dynamic>()
                   .Select(x => (IDictionary<string, object>)x)
                   .ToList();
    foreach (var record in dataRecords)
    {
    
    }
}

First Row

Second Row

LoadFile:

þInternal_File_IdþþIdþþFileNameþþEMAIL_FROMþþSubjectþþEMAIL_RECEIVED_DATE_TIMEþ
þ248073þþGRM00001504þþSCS CRUDE STRADDLES 11-19.msgþþAAA <aaa@mail.com>þþTest Mail 1þþ2001-04-17 04:13:00þ
þ248074þþGRM00001505þþPlease provide your NT Login Id and "_pc" Id RE: Vol Smil Authorization.msgþþAAA <aaa@mail.com>þþPlease provide your NT Login Id and "_pc" Id RE: Vol Smil Authorizationþþ2001-04-17 04:13:00þ
Solution

  • I believe it is because the double quote also defaults as the escape character. I changed the escape character to be the same as your quote character and it appears to be working correctly for me.

    Also it looks like you are using an older version of CsvHelper. The example I'm providing is for the newer version, but you should be able to add csv.Configuration.Escape = 'þ'; to your code.

    var config = new CsvConfiguration(CultureInfo.InvariantCulture){
    Delimiter = "¶",
    Quote = 'þ',
    Escape = 'þ'
};

using (var reader = new StreamReader(@"C:\Users\dspecht\Downloads\SampleDatFile_20230417_2.dat"))
using (var csv = new CsvReader(reader, config))
{
    csv.Read();
    csv.ReadHeader();
    List<IDictionary<string, object>> dataRecords = csv.GetRecords<dynamic>()
                   .Select(x => (IDictionary<string, object>)x)
                   .ToList();
    foreach (var record in dataRecords)
    {
        Console.WriteLine(record);
    }
}