CsvReader (using CSV Data Binder) does not appear to respect DateOnlyFormat or Culture options

[zejji]

No matter what I do, I can't get a CsvReader instance to parse a DateOnly in the format "dd/MM/yyyy".

Here's a minimal example to reproduce the issue which can be pasted into LINQPad. I'm using Sylvan.Data 0.2.8 and Sylvan.Data.Csv 1.2.4:

using Sylvan.Data;
using Sylvan.Data.Csv;

async Task Main()
{
	var data = """
"Settlement Date","Date","Symbol","Sedol","ISIN","Quantity","Price","Description","Reference","Debit","Credit","Running Balance"
"24/12/2022","24/12/2022","TEST","TESTV0","IE00TESTV02","100","£100.00","TEST","TEST04","£500.00","","£10.00"
""";

	var opts = new CsvDataReaderOptions
	{
		Culture = new CultureInfo("en-GB"),
		DateOnlyFormat = "dd/MM/yyyy",
	};

	using var csv = await CsvDataReader.CreateAsync(new StringReader(data), opts);
	var records = csv.GetRecords<Record>().ToList();
}

public class Record
{
	public DateOnly SettlementDate { get; set; }
	public DateOnly Date { get; set; }
	public string Symbol { get; set; }
	public string Sedol { get; set; }
	public string ISIN { get; set; }
	public int? Quantity { get; set; }
	public string Price { get; set; }
	public string Description { get; set; }
	public string Reference { get; set; }
	public string Debit { get; set; }
	public string Credit { get; set; }
	public string RunningBalance { get; set; }
}

[MarkPflug]

Looks like there is a bug in the Sylvan.Data binder when dealing with the new DateOnly/TimeOnly types. I've got a fix for that ready, and will push it shortly as 0.2.9.

However, there will also be a modification that needs to be made to your code. The issue is that the CsvDataReader doesn't "know" that any of the columns are a specific type. There are two ways to fix this.

One option is to specifically tell the CsvDataReader the types of the columns, by providing a "schema". Here is some test code demonstrating how to define the schema:

var data = "Name,Date\na,\"24/12/2022\"\nb,\"25/12/2022\"\n";

var schema =
	new Schema.Builder()
	.Add<string>("Name")
	.Add<DateOnly>("Date")
	// etc
	.Build();

var opts = new CsvDataReaderOptions
{
	DateOnlyFormat = "dd/MM/yyyy",
	Schema = new CsvSchema(schema)
};

using var csv = CsvDataReader.Create(new StringReader(data), opts);
var records = csv.GetRecords<DateOnlyRecord>().ToList();
Assert.Equal(new DateOnly(2022, 12, 24), records[0].Date);
Assert.Equal(new DateOnly(2022, 12, 25), records[1].Date);

The other option is to tell the databinder to use the type of the property being bound to identify the types of the columns in the source data. The GetRecords<T> method doesn't enable this by default, but it is easy to create your own GetRecords extension method that does:

public static IEnumerable<T> GetRecords<T>(this DbDataReader data)
	where T : class, new()
{
	var opts = new DataBinderOptions { 
		// tells the binder to use the member type
		// to determine the appropriate data accessor
		InferColumnTypeFromMember = true 
	};
	var binder = DataBinder.Create<T>(data, opts);
	while (data.Read())
	{
		var record = new T();
		binder.Bind(data, record);
		yield return record;
	}
}

I'll post an update when the package is published.

[MarkPflug]

Okay, 0.2.9 has been published. It might take a few minutes to show up on nuget.

[zejji]

Many thanks for the reply, that makes perfect sense