Can't parse last line of CSV file using Lumenworks CsvReader

Question

I'm trying to parse the following CSV file using LumenWorks CsvReader.

This is my code:

    using (DatabaseEntities context = new DatabaseEntities())
    {
        using (var csv = new CachedCsvReader(new StreamReader(@"C:\Users\Me\Desktop\sdn.csv"), false))
        {
            context.Database.ExecuteSqlCommand("TRUNCATE TABLE ofac_sdn");

            foreach (var entry in csv)
            {
                var ofac = new ofac_sdn
                {
                    ent_num = Convert.ToInt32(entry[0]),
                    SDN_Name = entry[1],
                    SDN_Type = entry[2],
                    Program = entry[3],
                    Title = entry[4],
                    Call_Sign = entry[5],
                    Vess_type = entry[6],
                    Tonnage = entry[7],
                    GRT = entry[8],
                    Vess_flag = entry[9],
                    Vess_owner = entry[10],
                    Remarks = entry[11]
                };

                context.ofac_sdn.Add(ofac);
            }
        }

        context.SaveChanges();

For all lines but the last one, I have no issues. Each entry inside the foreach contains each line's info separated correctly and my ofac_sdn instances are loaded with the right data.

However, the CSV file has something strange at the end of the file, as the image below shows (taken from Notepad++):

When the foreach gets to the last line, it throws the following exception:

"The CSV appears to be corrupt near record '5913' field '1 at position '0'. Current raw data : ''."

The simplest and easiest solution that I can think of is programatically delete the last line of the file before trying to parse it, but this looks like a "cheap" fix and won't solve the underlying issue. Any ideas?

Answer 1

I could not get this to work with Lumenworks CsvReader or pretty much any other library I put my hands on until I tried CsvHelper by Josh Close.

using (DatabaseEntities context = new DatabaseEntities())
{
    if (ofacFile != null)
    {
        var csv = new CsvReader(ofacFile);
        csv.Configuration.TrimFields = true;
        csv.Configuration.HasHeaderRecord = false;
        context.Database.ExecuteSqlCommand("TRUNCATE TABLE ofac_sdn");

        while (csv.Read())
        {
            if (csv.GetField<string>(0) == "\u001a")
            {
                break; // End of file
            }

            var ofac = new ofac_sdn
            {
                ent_num = csv.GetField<int>(0),
                SDN_Name = csv.GetField<string>(1),
                SDN_Type = csv.GetField<string>(2),
                Program = csv.GetField<string>(3),
                Title = csv.GetField<string>(4),
                Call_Sign = csv.GetField<string>(5),
                Vess_type = csv.GetField<string>(6),
                Tonnage = csv.GetField<string>(7),
                GRT = csv.GetField<string>(8),
                Vess_flag = csv.GetField<string>(9),
                Vess_owner = csv.GetField<string>(10),
                Remarks = csv.GetField<string>(11)
            };

            context.ofac_sdn.Add(ofac);
        }
    }
}

It was the only library that allowed me to actually read that last "corrupt" line, and although I don't really like the direct comparison to \u001a (which is what is written in that last line), it does work.