Messy files

Real files break the rules: stray quotes, quotes that never close, records with too many or too few fields, whitespace around values. By default the reader recovers from all of these and keeps reading, so that a file that opens in a spreadsheet also opens here. The options on this page make it stricter, or tell you what it found.

What the reader adapts to by default

For example, this file has a stray quote in its second record and text after a closing quote in its third:

Name,Height
Anna,5'10"
"Ben"x,6'
Cara,"5'4"

using var reader = CsvReader.FromFile("heights.csv");
while (reader.Read()) Console.WriteLine($"[{reader.GetString(0)}] [{reader.GetString(1)}]");

// [Anna] [5'10"]     the quotes are text: they are not at the start of the field
// [Benx] [6']        text after the closing quote is appended
// [Cara] [5'4]       a well-formed quoted field

Options for malformed files

Reporting malformed records

OnMalformedRecord is called for every malformed record the reader finds, with a CsvMalformedRecord that says what is wrong and where. The reader still recovers (or skips, or throws) as MalformedData says:

var options = new CsvReaderOptions
{
    OnMalformedRecord = bad => Console.WriteLine(
        $"Record {bad.RecordNumber}: {bad.Reason} at byte {bad.ByteOffset}, skipped: {bad.Skipped}: {bad.RawRecord}"),
};
using var reader = CsvReader.FromFile("heights.csv", options);
while (reader.Read()) { }

// Record 2: StrayQuote at byte 21, skipped: False: Anna,5'10"
// Record 3: TextAfterClosingQuote at byte 28, skipped: False: "Ben"x,6'

The callback runs on the thread that reads, one record at a time and in input order, also with ParallelEnumerate. Setting it turns on malformed-quote detection, which costs a little speed on heavily quoted data; without a callback and with MalformedData = Recover, the reader does not look for these problems at all.

Throwing or skipping

MalformedData = Throw stops at the first record with bad quoting. The records before it are returned normally, so you can import up to the problem and report it:

// Name,Height
// Anna,170
// "Ben"x,180
// Cara,160
var options = new CsvReaderOptions { MalformedData = CsvMalformedDataHandling.Throw };
using var reader = CsvReader.FromFile("heights.csv", options);
try
{
    while (reader.Read()) Console.WriteLine(reader.GetString(0));    // Anna
}
catch (CsvFormatException e)
{
    // Malformed CSV in record 3: text after the closing quote of a quoted field (byte offset 26).
    Console.WriteLine($"{e.Message} (record {e.RecordNumber}, byte {e.ByteOffset})");
}

MalformedData = Skip passes over records with bad quoting, and reports them to OnMalformedRecord with Skipped = true. The same file then reads as Anna and Cara.

Unbalanced quotes

A quoted field may contain line breaks, so a quote that is opened and never closed makes the rest of the file one field:

Name,Note
Anna,"forgot to close
Ben,ok
Cara,ok

using var reader = CsvReader.FromFile("notes.csv");
// One record: [Anna] [forgot to close\nBen,ok\nCara,ok\n]

If your files never have line breaks inside quotes, set AllowLineBreaksInQuotes = false: every line break then ends the record, and the damage stays on one line. The record with the open quote is reported as LineBreakInQuotes:

var options = new CsvReaderOptions { AllowLineBreaksInQuotes = false };
using var reader = CsvReader.FromFile("notes.csv", options);
// [Anna] [forgot to close]
// [Ben] [ok]
// [Cara] [ok]

Field counts

Records may have any number of fields by default. FieldCountMismatch checks every record against the header (or, without a header, the first record):

// A,B
// 1,2
// 3
// 4,5,6
// 7,8
var options = new CsvReaderOptions
{
    FieldCountMismatch = CsvFieldCountHandling.Skip,
    OnMalformedRecord = bad => Console.WriteLine($"Skipped: {bad.RawRecord}"),
};
using var reader = CsvReader.FromFile("counts.csv", options);
// Records 1,2 and 7,8; "Skipped: 3" and "Skipped: 4,5,6" are logged.

With CsvFieldCountHandling.Throw, the reader throws CsvFormatException (Record 3 has 1 fields but 2 were expected.) at the first record with a different count.

Missing fields

Without a field count check, a short record reads as if the missing fields were empty. MissingField = Throw makes reading such a field an error instead, while still allowing short records that you don't read past:

var options = new CsvReaderOptions { MissingField = CsvMissingFieldHandling.Throw };
using var reader = CsvReader.FromText("A,B,C\n1,2\n", options);
reader.Read();
reader.GetString(1);     // "2"
reader.GetString(2);     // IndexOutOfRangeException: Record 2 has 2 fields; field 2 does not exist.

Whitespace, comments and empty lines

• TrimWhitespace = true removes spaces and tabs around values and header names; a quote after leading whitespace still opens a quoted field ( "a, b" reads as a, b). TrimInsideQuotes = true also turns " a " into a, and WhitespaceCharacters trims other padding characters. See Whitespace.
• CommentPrefix = '#' skips lines that start with #, such as notes before the header. See Comments and empty lines.
• Empty lines are skipped; SkipEmptyLines = false returns each as a record with one empty field.