Csvhelper: How to handle empty column in parsing with CsvHelper?

[viveknuna]

I have a CSV file, I'm trying to parse it, But getting the below error on int datatype column in the file, I'm passing the blank value for this column.

CsvHelper Version=6.0.0.0

[AutoMap(typeof(Test))]
[FileEntity("Test")]
public class TestFileEntity : BaseFileEntity
{
    [CsvFileColumn(1)]
    public int TestId { get; set; }
}

public IEnumerable<BaseFileEntity> Parse(Stream masterData, Type entityType, out IEnumerable<MasterDataFileParserError> errors)
{
    var list = new List<BaseFileEntity>();
    var errorList = new List<MasterDataFileParserError>();

    try
    {
        using (var reader = new StreamReader(masterData))
        {
            var parser = new CsvReader(reader, new CsvHelper.Configuration.Configuration
            {
                HasHeaderRecord = true,
                HeaderValidated = null,
                MissingFieldFound = null
            });

            list.AddRange(parser.GetRecords(entityType).Cast<BaseFileEntity>());
        }
    }
    catch (Exception ex)
    {
        if (ex.Data == null || ex.Data.Count == 0)
        {
            errorList.Add(new MasterDataFileParserError
            {
                Error = ex.Message,
                Description = ex.ToString()
            });
        }
        else
        {
            foreach (var key in ex.Data.Keys)
            {
                errorList.Add(new MasterDataFileParserError
                {
                    Error = ex.Message,
                    Description = $"{key}: {ex.Data[key]}"
                });
            }
        }
    }

    errors = errorList;

    return list;
}

Exception in this line: list.AddRange(parser.GetRecords(entityType).Cast<BaseFileEntity>());

Error:

An error has occurred while parsing 'xyz.csv' file for 'Test' entity: '[{"Error":"The conversion cannot be performed.\r\n Text: ''\r\n
MemberType: System.Int32\r\n TypeConverter: 'CsvHelper.TypeConversion.Int32Converter'","Description":"CsvHelper.TypeConversion.TypeConverterException: The conversion cannot be performed.\r\n Text: ''\r\n MemberType: System.Int32\r\n TypeConverter: 'CsvHelper.TypeConversion.Int32Converter'\r\n at CsvHelper.Configuration.Configuration.<>c.<.ctor>b__148_4(CsvHelperException exception)\r\n at CsvHelper.CsvReader.d__65.MoveNext()\r\n at System.Linq.Enumerable.d__341.MoveNext()\r\n at
System.Collections.Generic.List1.AddEnumerable(IEnumerable1
enumerable)\r\n at
System.Collections.Generic.List1.InsertRange(Int32 index, IEnumerable1 collection)\r\n at
Nec.Stanchion.Business.MasterDataFiles.Handling.MasterDataFileParser.Parse(Stream
masterData, Type entityType, IEnumerable1& errors) in xyz.cs file"}]'.

The reason is very clear, that blank or empty string is not compatible with int datatype, so there has to be some way to allow empty or null value with int datatype column value.

[JoshClose]

If you have null values for that property, then change the property to int? and it should work fine.

[viveknuna]

I have tried making property nullable( int?), but it's giving the same error.

[JoshClose]

You'll need to give me a self contained failing example. This works for me.

void Main()
{
    var config = new CsvHelper.Configuration.Configuration
    {
        HasHeaderRecord = true,
        HeaderValidated = null,
        MissingFieldFound = null,
        IgnoreBlankLines = false
    };
    using (var stream = new MemoryStream())
    using (var writer = new StreamWriter(stream))
    using (var reader = new StreamReader(stream))
    using (var csv = new CsvReader(reader, config))
    {
        writer.WriteLine("TestId");
        writer.WriteLine("1");
        writer.WriteLine("");
        writer.WriteLine("3");
        writer.Flush();
        stream.Position = 0;

        csv.GetRecords(typeof(TestFileEntity)).Cast<BaseFileEntity>().Dump();
    }
}

public abstract class BaseFileEntity
{    
}

public class TestFileEntity : BaseFileEntity
{
    public int? TestId { get; set; }
}

[viveknuna]

Thanks for ya response.

I have checked your code, it’s working correctly, I will check my code once again and debug it and will keep you posted.

I have one more question, exception does not show field or property information. Like let’s say there are 100 properties and it’s failing for ~50 properties. By seeing exception it’s not clear that for which properties it’s failing.

[JoshClose]

CsvHelperException has some properties on it that might be useful. ReadingContext and WritingContext will have the current state of the system. If it happened in a TypeConverterException, you'll also get the Text and Value that was trying to be converted, along with a couple other properties.

[vbebins]

This one works...saved my time. thanks a lot

[roddharris]

@viveknuna Did you figure out what your problem was? I have the exact same thing going on -- a property defined as int? mapped to a column that sometime has spaces and I get an error that it cannot be mapped.

I even tried subclassing Int32Converter and overriding the ConvertFromString() method.

public class MyInt32Converter : Int32Converter {
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        text = text.Trim();

        //Set a debugger with the condition of string.IsNullOrEmpty(text) on the following line
        //Confirmed that text is empty before calling base !!
        return base.ConvertFromString(text, row, memberMapData);
    }
}

//In my configuration
reader.Configuration.TypeConverterCache.RemoveConverter<int>();
reader.Configuration.AddConverter<int>(new MyInt32Converter());

[ericjohannsen]

I ran into this kind of problem when the CSV contained a space. That is:
, , ,
causes converter problems, while
,,,
does not.

[JoshClose]

You can try

csv.Configuration.TrimOptions = TrimOptions.Trim;

[hardikce015]

@viveknuna Did you figure out what your problem was? I have the exact same thing going on -- a property defined as int? mapped to a column that sometime has spaces and I get an error that it cannot be mapped.

I even tried subclassing Int32Converter and overriding the ConvertFromString() method.

public class MyInt32Converter : Int32Converter {
    public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
    {
        text = text.Trim();

        //Set a debugger with the condition of string.IsNullOrEmpty(text) on the following line
        //Confirmed that text is empty before calling base !!
        return base.ConvertFromString(text, row, memberMapData);
    }
}

//In my configuration
reader.Configuration.TypeConverterCache.RemoveConverter<int>();
reader.Configuration.AddConverter<int>(new MyInt32Converter());

I have the same problem.
Below is the exception data. it does not show any field or value it is trying to parse.

The conversion cannot be performed.
Text: ''
MemberType:
TypeConverter: 'CsvHelper.TypeConversion.Int32Converter'

the csv file is following.

[lexdavis030]

Bump. Has this been solved? I have something similar and I've tried the trim options I've tried skipping the record where there is a null string, empty string, or white space string and none of it works. Below is what I have, also I'm attempting to enumerate through my records.

[JoshClose]

Can you post that 1 line CSV file here?

[lexdavis030]

I decided to just configure my mapping as all strings and as I iterate through when/if I need to convert the strings to ints I'll do it then. But for the sake of productivity which line again. I'm sorry I'm using my smart phone and can't see well.

[JoshClose]

The Excel screen shot. I'd like to try to run this with the example data.

[lexdavis030]

Here you go

[JoshClose]

I mean a CSV file that causes this failure. Like

PartNum,Quantity
test,10

but with the actual data that fails.

[JoshClose]

Preferably a 2 column 2 row file like in your Excel example.

[lexdavis030]

LastName, FicoScore
Jones, 
Williams, 640

[JoshClose]

Do your fields all have a space before the text except for the first one?

[JoshClose]

This example works. Can you edit this example to show the problem you're having?

void Main()
{
    var s = new StringBuilder();
    s.AppendLine("LastName, FicoScore");
    s.AppendLine("Jones, ");
    s.AppendLine("Williams, 640");
    using (var reader = new StringReader(s.ToString()))
    using (var csv = new CsvReader(reader))
    {
        csv.Configuration.TrimOptions = TrimOptions.Trim;
        csv.GetRecords<Foo>().ToList().Dump();
    }
}

public class Foo
{
    public string LastName { get; set; }
    public int? FicoScore { get; set; }
}

[lexdavis030]

All of my empty cells had a single space in them and it was causing an issue whenever I type cast them as integers saying it can't convert blank space.

[jzabroski]

If you have null values for that property, then change the property to int? and it should work fine.

I think the reason people keep coming back to this old thread is because of a few reasons:

1. Converting from a non-numeric value to a non-nullable value type will throw an exception
2. The TypeConverterOption.NullValues() interface doesn't let you specify a Regex to ignore all whitespace.
3. .Default(-1) only works if CsvHelper _knows_ the value is null.
4. It's possible csv.Configuration.TrimOptions = TrimOptions.Trim doesn't actually trim everything cf #1257, so using it in conjunction with TypeConverterOption.NullValues(string.Empty) doesnt gaurantee a base case of "trimmed down to string.Empty if it contains whitespace"
5. When I get a parsing error, for some reason, Index is set in the exception object, but Field is not. (I think I recall the root cause is any CSV file with multi-line rows allowed cf #1312, but would need to repro to confirm.) UPDATE: This happens when you get a type converter exception, because it's not regarded as "BadDataFound" event (see next point below).
6. Type conversion errors are not regarded as 'BadDataFound' events.
7. Default(object value) uses an object value, rather than the type of the Member property in Map(m => m.Member), which can generate other exceptions like Default of type 'System.Int32' does not match member of type 'System.String'. Thankfully, this happens at the time you call RegisterClassMap, so it's somewhat easy to troubleshoot.

However, _in spite of all this_, yes, in theory, you can convert a blank value into a default value on a non-nulllable value type, as the below updated example demonstrates. The question is - How can you display the value of the blank input?

It might be nice if there was an option for, when you throw an exception on a type converter, to print the field value using C# Unicode Literal. Something like csv.Configuration.PrintValuesAsCSharpUnicodeLiteral = true - that might also give you easier time supporting this library, as I suspect a fair number of support requests come around massaging input.

```c#
void Main()
{
var s = new StringBuilder();
s.AppendLine("LastName, FicoScore");
s.AppendLine("Jones, ");
s.AppendLine("Williams, 640");
using (var reader = new StringReader(s.ToString()))
using (var csv = new CsvReader(reader))
{
csv.Configuration.TrimOptions = TrimOptions.Trim;
csv.Configuration.BadDataFound = (context) =>
{
Console.WriteLine(context.RawRecord);
ConfigurationFuncitons.BadDataFound(context); // let CsvHelper throw this.
};
csv.Configuration.RegisterClassMap();
csv.GetRecords().ToList().Dump();
}
}

public sealed class FooMap : ClassMap
{
public FooMap()
{
Map(m => m.Id).Name("Id").Default(-1).TypeConverterOptions.NullValue(string.Empty);
Map(m => m.Name).Name("The Name");
}
}

public class Foo
{
public string LastName { get; set; }
public int? FicoScore { get; set; }
}
```

[jzabroski]

@JoshClose I tried to work through a whole bunch of scenarios tonight, please see previous post for some thoughts.