How to handle invalid date format

[Jayz58]

This is an example of some of my data

Amortization_type,Balloondate,Book_value,Day_count_convention,Deal_date
Bullet,00:00.0,218668,30/360,00:00.0

As you can see the date is 00:00.0 - I'm getting the following error

{System.FormatException: String '00:00.0' was not recognized as a valid DateTime.
   at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
   at System.DateTime.Parse(String s, IFormatProvider provider, DateTimeStyles styles)
   at CsvHelper.TypeConversion.DateTimeConverter.ConvertFromString(String text, IReaderRow row, MemberMapData memberMapData)
   at CsvHelper.TypeConversion.NullableConverter.ConvertFromString(String text, IReaderRow row, MemberMapData memberMapData)
   at lambda_method(Closure )
   at CsvHelper.Expressions.RecordCreator.Create[T]()
   at CsvHelper.CsvReader.GetRecords[T]()+MoveNext()}

I've tried handling ReadingExceptionOccurred and BadDataFound but they don't get called. I need to find a way to detect and handle/ignore such format errors. I'm using version 12.10.0 of CsvHelper and .NET Core 2.1

Thanks

[hybridherbst]

I also have that question, on my end with empty fields that the SingleConverter can't parse. Neither BadDataFound nor ReadingExceptionOccured nor MissingFieldFound seem to get called.

TypeConverterException: The conversion cannot be performed.
    Text: ''
    MemberType: System.Single
    TypeConverter: 'CsvHelper.TypeConversion.SingleConverter'
CsvHelper.TypeConversion.DefaultTypeConverter.ConvertFromString (System.String text, CsvHelper.IReaderRow row, CsvHelper.Configuration.MemberMapData memberMapData) (at <c178bca66479415ba312402272e6daa5>:0)
CsvHelper.TypeConversion.SingleConverter.ConvertFromString (System.String text, CsvHelper.IReaderRow row, CsvHelper.Configuration.MemberMapData memberMapData) (at <c178bca66479415ba312402272e6daa5>:0)

[JoshClose]

One option would be to make the DateTime properties Nullable and treat the 00:00.0 as null.

void Main()
{
	var s = new StringBuilder();
	s.AppendLine("Id,Name,Date");
	s.AppendLine("1,one,00:00.0");
	s.AppendLine("2,two,00:00.0");
	using (var reader = new StringReader(s.ToString()))
	using (var csv = new CsvReader(reader))
	{
		csv.Configuration.RegisterClassMap<FooMap>();
		csv.GetRecords<Foo>().ToList().Dump();
	}
}

public class Foo
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime? Date { get; set; }
}

public class FooMap : ClassMap<Foo>
{
	public FooMap()
	{
		Map(m => m.Id);
		Map(m => m.Name);
		Map(m => m.Date).TypeConverterOption.NullValues("00:00.0");
	}
}

Another option would be to create a custom date time converter. You can handle the values however you want then. here is an example.

void Main()
{
	var s = new StringBuilder();
	s.AppendLine("Id,Name,Date");
	s.AppendLine("1,one,00:00.0");
	s.AppendLine("2,two,00:00.0");
	using (var reader = new StringReader(s.ToString()))
	using (var csv = new CsvReader(reader))
	{
		csv.Configuration.RegisterClassMap<FooMap>();
		csv.GetRecords<Foo>().ToList().Dump();
	}
}

public class Foo
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime Date { get; set; }
}

public class FooMap : ClassMap<Foo>
{
	public FooMap()
	{
		Map(m => m.Id);
		Map(m => m.Name);
		Map(m => m.Date).TypeConverter<CustomDateTimeConverter>();
	}
}

public class CustomDateTimeConverter : DateTimeConverter
{
	public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
	{
		if (text == "00:00.0")
		{
			return default(DateTime);
		}

		try
		{
			return base.ConvertFromString(text, row, memberMapData);
		}
		catch (TypeConverterException)
		{
			return default(DateTime);
		}
		catch
		{
			throw;			
		}
	}
}

You could also do it inline using ConvertUsing.

void Main()
{
	var s = new StringBuilder();
	s.AppendLine("Id,Name,Date");
	s.AppendLine("1,one,00:00.0");
	s.AppendLine("2,two,00:00.0");
	using (var reader = new StringReader(s.ToString()))
	using (var csv = new CsvReader(reader))
	{
		csv.Configuration.RegisterClassMap<FooMap>();
		csv.GetRecords<Foo>().ToList().Dump();
	}
}

public class Foo
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime Date { get; set; }
}

public class FooMap : ClassMap<Foo>
{
	public FooMap()
	{
		Map(m => m.Id);
		Map(m => m.Name);
		Map(m => m.Date).ConvertUsing(row =>
		{
			var field = row.GetField(nameof(Foo.Date));
			if (field == "00:00.0")
			{
				return default(DateTime);
			}
			
			return DateTime.Parse(field);
		});
	}
}

I would suggest using a custom type converter instead of this option though.

[hybridherbst]

Thanks for the suggestions, but I think the underlying question here is "if I use the configuration options and decide to ignore missing/empty/wrongly formatted data, why does the reader still throw errors and doesn't just either skip those rows or use the default value for those fields?".

[JoshClose]

Because you should know when something is invalid, and specifically handle it. It's not much code to write a custom converter to handle things.

Also, you can do this to skip it.

void Main()
{
	var s = new StringBuilder();
	s.AppendLine("Id,Name,Date");
	s.AppendLine("1,one,00:00.0");
	s.AppendLine("2,two,1/15/2019");
	using (var reader = new StringReader(s.ToString()))
	using (var csv = new CsvReader(reader))
	{
		csv.Configuration.ReadingExceptionOccurred = ex =>
		{
			return false;
		};
		csv.GetRecords<Foo>().ToList().Dump();
	}
}

public class Foo
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DateTime Date { get; set; }
}

Output:

[NickBeukema]

I'm having a similar issue with a 600+ column entity. Is there any way to register a global type converter for DateTime?

[JoshClose]

csv.Configuration.TypeConverterCache.AddConverter<DateTime>(new MyDateTimeConverter());

[sseyalioglu]

@JoshClose I also agree with the other guys here. Instead of writing custom converter, it would be nice to have default value when exception happens on reading due to format instead of completely skipping the row.

[JoshClose]

I just added #1404. I'm not positive it's something I'm going to do, but I'll investigate the possibility.