Convert to decimal when using CsvHelper

So the issue is that CSVHelper doesn't understand how to convert an empty field for LensBespokePrice to a decimal value. There are two options you can use here:

1. Update the CSV file to add a default value to the empty fields (i.e. 0 for LensBespokePrice).
2. Create a Type Conversion to handle an empty cell to a decimal.

Do you have the ability to modify the CSV file? If so, then #1 works by changing your CSV to be (note the change for LensBespokePrice and CareOnly):

Title,Amount,NHS,Reference,GoCardless ID,email,surname,firstname,Full Name,DOB,Age,Right Lens,Left Lens,RightLensMonthlyAmount,LeftLensMonthlyAmount,LensMonthlyAmount,FeeMonthlyAmount,VAT Basis,LensBespokePrice,CareOnly,Notes
Mrs,24.3,N,100247,CUXXX,email@gmail.com,User,Test,Test User,17/09/1957,64,DAILIES® AquaComfort PLUS 30 Pack,DAILIES® AquaComfort PLUS 30 Pack,16.5,16.5,33,6.35,,0,N,

If not, you'll need a type converter for both the empty decimal and empty boolean. For example, with all your code in a single file, that may look like:

using CsvHelper;
using CsvHelper.Configuration;
using CsvHelper.Configuration.Attributes;
using CsvHelper.TypeConversion;
using System;
using System.Globalization;
using System.IO;
using System.Text.Json;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            var culture = new CultureInfo("en-GB");
            var config = new CsvConfiguration(culture);
            

            using (var reader = new StreamReader("test.csv"))
            using (var csv = new CsvReader(reader, config))
            {
                var records = csv.GetRecords<Payer>();
                Console.WriteLine("Got records"); //this prints on the console
                foreach (var payer in records)
                {
                    var j = JsonSerializer.Serialize(payer);
                    Console.WriteLine(j);
                }
            }
        }
    }

    public class CustomDecimalConverter : DecimalConverter
    {
        public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
        {
            if(decimal.TryParse(text, out var result))
            {
                return result;
            } else
            {
                return decimal.Zero;
            }
        }
    }

    public class CustomBooleanConverter : BooleanConverter
    {
        public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
        {
            if (bool.TryParse(text, out var result))
            {
                return result;
            }
            else
            {
                return false;
            }
        }
    }

    public class Payer
    {
        public string Title { get; set; }
        public decimal Amount { get; set; }

        [BooleanTrueValues("Y")]
        [BooleanFalseValues("N")]
        public bool NHS { get; set; }

        public string Reference { get; set; }

        [Name("GoCardless ID")]
        public string GoCardless_ID { get; set; }

        public string email { get; set; }
        public string surname { get; set; }
        public string firstname { get; set; }

        [Name("Full Name")]
        public string Fullname { get; set; }

        [Name("DOB")]
        public string Dob { get; set; }

        public int Age { get; set; }

        [Name("Right Lens")]
        public string RightLens { get; set; }
        [Name("Left Lens")]
        public string LeftLens { get; set; }

        public decimal RightLensMonthlyAmount { get; set; }
        public decimal LeftLensMonthlyAmount { get; set; }
        public decimal LensMonthlyAmount { get; set; }
        public decimal FeeMonthlyAmount { get; set; }

        [Name("VAT Basis")]
        public string VATBasis { get; set; }

        [TypeConverter(typeof(CustomDecimalConverter))]
        public decimal LensBespokePrice { get; set; }

        [BooleanTrueValues("Y")]
        [BooleanFalseValues("N")]
        [TypeConverter(typeof(CustomBooleanConverter))]
        public bool CareOnly { get; set; }

    }
}

Please note that I added the JsonSerializer.Serialize(payer); in the foreach loop within the Main method so that you can view the JSON result from the console.

I added in two custom converters (CustomBooleanConverter and CustomDecimalConverter). The payer class is then updated to have attributes added to the LensBespokePrice and CareOnly properties. Additionally, you didn't have an attribute on CareOnly for false values and while not required is a good practice.

To clarify why this is happening only in your foreach loop and not var records = csv.GetRecords<Payer>(); is because the values aren't actually converted into your payer class until the records are enumerated.

Payer is a whole class of values. It looks like it is trying to convert when you write to the console. I believe you will need to tell it what part of payer you are wanting to print like:

Console.WriteLine(payer.surname);