Initialize DTO properties with default value or null.

[SteveLemire]

Hi!!! I have few requirements to minimize the errors while reader CSV and Excel from an IFormFile. The goal is to automatically fix bad data and assign DTO property default value when the parsing failed. I would like to know how to initialize DTO properties to a default value (null, date now, enum, etc.) depending on data type and/or [DefaultValue] attribute.

I also need to be able to get all the failed parsed columns. Currently it seems to stop processing the columns when the first column failed to parse.

How can I initialize DTO properties if parsing failed and also get all the failed columns for each row?

var dataBinderOptions = new DataBinderOptions()
{
    // AllColumns is required to validate header column name map to an existing entity property.
    BindingMode = DataBindingMode.AllColumns,

    // Convert field to TEntity property type must be false
    // do not set to true because it have a huge impact on the binder performances
    InferColumnTypeFromMember = false,
};

var binder = DataBinder.Create<TEntity>(dataReader, this.dataBinderOptions);
while (dataReader.Read())
{
var entity = binder.GetRecord<TEntity>(dataReader, this.HandleDataReaderException);
// REQUIREMENT: retrieve all failed parsed properties, initialize them to a default value (or null)
}

[MarkPflug]

I also need to be able to get all the failed parsed columns

I'm not sure what you mean by this. What part of the process is failing?

[SteveLemire]

If have the following CSV:

LastName,FirstName,Gender,Address,Email,BirthDate
My Last Name,My Firt Name,Male,1234 My Street,[email protected],0000-00-00

Then binder.GetRecord<TEntity> where all column's name map to one of the entity property, then Address, Gender and Email are all null!

I would expect that only failed parsed columns (BirthDate) will be null.

I hope this is clearer!

[MarkPflug]

Can you share the type definition for "TEntity"? The concrete type that is being used for that type parameter.

[SteveLemire]

I can share the concrete type of TEntity but as I said, every name on the header row of the CSV map to one of the entity property.

    public class Contact : IEntity
    {
        [Required]
        [StringLength(50)]
        [Display(Order = 2, GroupName = Constants.DisplayGroupName.Contact)]
        [DefaultSort]
        public string FirstName { get; set; }

        [Required]
        [StringLength(50)]
        [Display(Order = 3, GroupName = Constants.DisplayGroupName.Contact)]
        public string LastName { get; set; }

        [Required]
        [DataType(DataType.Date)]
        [Display(Order = 4, GroupName = Constants.DisplayGroupName.Contact)]
        public DateTime? BirthDate { get; set; }

        [DefaultValue(ContactGender.Unspecified)]
        [EnumDataType(typeof(ContactGender))]
        [Display(Order = 5, GroupName = Constants.DisplayGroupName.Contact)]
        public ContactGender Gender { get; set; }

        [Required]
        [StringLength(150)]
        [DataType(DataType.EmailAddress)]
        [Display(Order = 6, GroupName = Constants.DisplayGroupName.Contact)]
        public string Email { get; set; }

        [StringLength(100)]
        [Display(Order = 11, GroupName = Constants.DisplayGroupName.Location)]
        public string Address { get; set; }

Not sure if that could helps diagnose the behavior but I use the following csv options:

        private CsvDataReaderOptions csvOptions = new CsvDataReaderOptions()
        {
            HasHeaders = true,
            HeaderComparer = StringComparer.InvariantCultureIgnoreCase,
            Schema = CsvSchema.Nullable,
            StringFactory = new StringPool().GetString,
        };

Thanks!

[MarkPflug]

Thanks, this makes sense. The binder doesn't track what has been assigned, so when a failure happens it doesn't have a means to communicate what properties were successfully assigned or not. An exception is thrown, and subsequent properties are skipped. I'm pretty sure the declaration order of the properties is why you are seeing the other values as null.

What I'd recommend, is to create your own wrapper DbDataReader that can provide some custom validation behavior. Every field accessor can delegate to the inner data reader and handle the error.

An incomplete example:

sealed class ValidatingDataReader : DbDataReader
{
    DbDataReader inner;
    bool[] errors;
    bool hasErrors;
    Action<bool[]> errorCallback;

    public ValidatingDataReader(DbDataReader inner, Action<bool[]> errorCallback)
    {
        this.inner = inner;
        this.errors = new bool[inner.FieldCount];
    }

    public override bool Read()
    {
        if (hasErrors)
        {
            errorCallback(this.errors);
        }
        this.hasErrors = false;
        Array.Clear(this.errors);
        return inner.Read();
    }

    void ValidateOrdinal(int ordinal)
    {
        if (ordinal < 0 || ordinal >= this.inner.FieldCount)
            throw new ArgumentOutOfRangeException();
    }

    public override DateTime GetDateTime(int ordinal)
    {
        ValidateOrdinal(ordinal);

        try
        {
            return inner.GetDateTime(ordinal);
        }
        catch // don't really care what the exception is
        {
            hasErrors = true;
            errors[ordinal] = true;
            return DateTime.MinValue;
        }
    }
}

[SteveLemire]

My goal is to provide user hint about bad formatted excel file and what type of conversion failed with no repetition validation of each type.

Since I'm not able to evaluate each cell and provide my own local validation without throwing exception that's make the code poorly perform, I had to loop the DbDataReader schema columns and map it with my TEntity properties type using reflection and doing my own conversion to assign each entity property value.

It works well until I wanted to evaluate if a cell contains bad formula (#N/A, #NAME, etc.). I cannot find a way to get the excel cell raw value because the ExcelDbReader return null (when GetErrorAsNull = true) or throw ExcelFormulaException (when GetErrorAsNull = false).

I would like to be able to use the following method:

ref CellData GetCell(int ordinal)

Please can you tell me how can I get the excel cell raw value just like the CsvDbDataReader.

For now I will use a converter from ExcelErrorCode to the raw value but I'm pretty sure there is another solution.

var cellValue = this.excelReader.GetExcelValue(columnIndex);
if(cellValue is ExcelErrorCode)
{
    return ConvertErrorCodeToString(cellValue);
}
return cellValue;

PS: Because the CsvDbDataReader works more like I need, I'm about to try to convert excel (xls and xlsx) to CSV!

Thanks!

[MarkPflug]

I'm not sure exactly what you mean by "raw value". You can access the internal Excel type of a cell with GetExcelDataType(int ordinal). When that method returns ExcelDataType.Error, you can call GetFormulaError(int ordinal) to get the error code. You can map the error code to whatever string value you want, which should allow you to get "#N/A" or "#NAME". This would be slightly better than calling GetExcelValue, which boxes the ExcelErrorCode value.

I won't be changing the behavior of Excel errors causing exceptions to be thrown when you call GetValue or other accessors. Errors are errors and should be fixed in the spreadsheet or explicitly handled in the code.

[SteveLemire]

Thanks!!! I found the GetExcelDataType in your source code so I did exactly what you suggested:

        public object GetCellData(int columnIndex)
        {
            if (this.isCsvDataReader)
            {
                return this.csvReader[columnIndex];
            }

            var cellType = this.excelReader.GetExcelDataType(columnIndex);
            switch (cellType)
            {
                case ExcelDataType.Numeric:
                case ExcelDataType.DateTime:
                case ExcelDataType.String:
                case ExcelDataType.Boolean:
                    return this.excelReader[columnIndex];
                case ExcelDataType.Error:
                    return GetExcelFormulaValue(this.excelReader.GetFormulaError(columnIndex));
                case ExcelDataType.Null:
                default:
                    return null;
            }
        }

        public static string GetExcelFormulaValue(ExcelErrorCode errorCode)
        {
            switch (errorCode)
            {
                case ExcelErrorCode.Null:
                    return "#NULL!";
                case ExcelErrorCode.DivideByZero:
                    return "#DIV/0!";
                case ExcelErrorCode.Value:
                    return "#VALUE!";
                case ExcelErrorCode.Reference:
                    return "#REF!";
                case ExcelErrorCode.Name:
                    return "#NAME?";
                case ExcelErrorCode.Number:
                    return "Bad range number";
                case ExcelErrorCode.NotAvailable:
                    return "#N/A";
                default:
                    return "Unkown error";
            }
        }