Are there any established processes for error handling when parsing CSVs with binding?

[ChairmanMawh]

From a quick browse of the code, it looks like parsing a CSV just crashes and stops if problem data is encountered. Has any thought been put to perhaps having events that are raised if a malformed line is encountered? Or perhaps giving a handling delegate the opportunity to fix the line too, and it can be reparsed (or if the delegate returns null, skip this line, if it throws an exception, halt processing)..

[acher1965]

That could be useful (assuming it doesn't kill performance) In my case, the delegate would probably just log the error, happy to skip the record.

[MarkPflug]

There is no support for recovering from malformed CSV files, in much the same way that System.Xml doesn't provide a mechanism to repair or recover from "malformed XML". A file is either valid XML or it isn't, and I think the same philosophy should apply to CSV files. There are other libraries that try to provide support for those scenarios, but I'm not interested in dealing with it in my library.

A valid CSV file might have other issues that prevent it from processing correctly. Most frequently this happens when a column is expected to be a specific type, like a number or a date, but a value in the file doesn't conform to that schema. I have given some thought to that scenario, but the code is somewhat half-baked at the moment.

I guess I'd need to know what specific scenarios you want to handle.

[acher1965]

<<but a value in the file doesn't conform to that schema. I have given some thought to that scenario,>>
great - that would be alrerady v useful, and even a restricted version of that, dealing with the "missing" case - let me try to explain (sorry if verbose).

At the moment if the schema declares a column to be double, int, bool and the field is missing in a row, an exception will occur.
One needs to declare it nullable (i.e. double?, int?, bool?), the exception won't occur, a null will be read in, and the user has to post-process in order to turn that null into some kind of default parameter.
If would be great if instead the type could remain double, int, bool, but in CsvDataReaderOptions one there were more options: one each to define what the default value ( i.e. for missing doubles in most cases one would want double.NaN, in a few cases 0 instead; etc), and a bool each to define whether the defaultIfMissing behaviour is switched one or not - no post-processing needed (at least for the nullable), and simpler primitive types rather than nullable so better performance.
Of course one could extend a bit more, and let the defaultIfMissing behaviour be defined in the schema, so that different columns could have different default-behaviour.

For what regards the case when an exception is thrown because a record is rejected for some other reason (field is of bad type/format, not enough fields, etc etc) I'd be happy with a simple option to switch behaviour from throwing to skipping; even better if it called a user-defined delegate, with a few arguments e.g. (int recordNumber, int columnFailing, enum reasonWhyRejected) which allows the user to log the problem for separate investigation.
One of the advantages is that it makes is much easier to investigate what the problems might be with a given csv file, as all of the skipped lines would be reported in the logfile, with a meaningful reason, rather than having to run it many times to find all the failing rows...
Another advantage is that it makes Sylvan more usable in production-quality systems, where a CSV file might have only a small % of lines are failing, but the rest is perfectly usable and enough to continue.

[acher1965]

I'll try it and get back in the next few days.
Will it run against the package in nuget, or do I need to build a different branch locally?

[MarkPflug]

The latest package is in nuget. The specific versions are commented in the gist.

[acher1965]

Sorry Mark this is what I see in VisualStudio NuGet Manager, it doesn't give me 0.2.12 which if I understand correctly is what is needed to run that gist?

[MarkPflug]

You need to click the "Include prerelease" checkbox.

[acher1965]

I couldn't see it ... thanks. I've got it to work, I'll get back with feedback

[acher1965]

I have now played with the above a bit, here's my 0.02$.
For my use-cases, it 's nearly all there. I manage to define a schema (without nullables, so saving some post-proc), detect most of the missing, and set them to some externally defined default values, or skip the whole record if some necessary ones are not there.
But:

1. Maybe I am misssing something obvious, but I cannot detect when a field of type string is missing
2. The GetErrors() method returns IEnumerable, which (I guess) are the ordinals in the csv files - with that, the error-processor is specific to the particular "flavour" of the csv, not general to the schema.
   Maybe a similar method returning IEnumerable, i.e. the column names in the schema (the remapped ones, not the ones in the header of the csv files) would allow to define a single error-processor

[MarkPflug]

Thanks for the feedback. I'll have to give this some consideration.

[ChairmanMawh]

"Maybe I am misssing something obvious, but I cannot detect when a field of type string is missing"

What do you mean by "missing"? There is a cell without a value, or there is no cell?

Name,Age,HireDate
John,30,01-01-2020
Jim,,01-01-2020
Mary,01-01-2020

,30,01-01-2020
"",30,01-01-2020
30,01-01-2020

Which one(s) are missing the age? Which ones are missing the name?

And for strings being missing, is it a case of not being able to determine if empty string is a valid value or not? Are you drawing a distinction between "",30 and ,30? Is one null and the other empty? Is it conceivable that strings may not report as missing because they could be interpreted as something else in nearly all cases? (String can accept any value in the CSV. In the last line above the 30 is the name?)

[acher1965]

By "missing string" I meant empty field - so in your example the row
,30,01-01-2020
is the only one with missing Name (at least for my use-cases, it's OK that "" or 30 are passed on as valid strings, as I need to validate them anyway late, with complex logic that often depend on the values of other columns)

[MarkPflug]

The CsvDataReader never considers a column of type string to be invalid. A nullable string column will consider empty fields to be null, and a non-nullable string column will read empty fields as an empty string. So, there really isn't a way to cause this to be an error an error solely based on the schema definition.

However, I added a second method that should enable your scenario. Call Validate instead of ValidateSchema and the validation callback method will be called for every row instead of only for rows that have errors. This would allow you to detect when certain fields are empty or null and provide a default value.

Concerning your other comment about ValidationContext.GetErrors returning the ordinals instead of names, you can use the ordinal to look up the column name, then switch on the column name instead. I've updated the gist to demonstrate this:
https://gist.github.com/MarkPflug/6df6c1dee306685edec9a3293a83a0c0

You can experiment with this using version 0.2.12-B0002: https://www.nuget.org/packages/Sylvan.Data/0.2.12-B0002, which I just published.

[acher1965]

Tx - I'll give feedback asap