When I try to use CsvHelper to parse the below CSV, I get a "conversion cannot be performed" error (full error is below). It looks like I'm missing something about how to deal with reading values as decimals. Have seen some other answers relating to setting the culture, but that doesn't seem to have helped.
The CSV data is:
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 protected],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,,,,
My class to map this data to properties is:
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; }
public decimal LensBespokePrice { get; set; }
[BooleanTrueValues("Y")]
public bool CareOnly { get; set; }
}
My code related to parsing the CSV is:
static void Main(string[] args)
{
var culture = new CultureInfo("en-GB");
var config = new CsvHelper.Configuration.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)
{
Console.WriteLine(payer);
}
}
}
The error only happens with the foreach loop, not the actual GetRecords() method.
Full error:
CsvHelper.TypeConversion.TypeConverterException: "The conversion cannot be performed.\n Text: ''\n MemberType: System.Decimal\n TypeConverter: 'CsvHelper.TypeConversion.DecimalConverter'\nIReader state:\n ColumnCount: 0\n CurrentIndex: 18\n HeaderRecord:\n["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"]\nIParser state:\n ByteCount: 0\n CharCount: 392\n Row: 2\n RawRow: 2\n Count: 21\n RawRecord:\nMrs,24.3,N,100247,CUXXX,[email protected],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,,,,\r\n\n" at CsvHelper.TypeConversion.DefaultTypeConverter.ConvertFromString(String text, IReaderRow row, MemberMapData memberMapData)\n at CsvHelper.TypeConversion.DecimalConverter.ConvertFromString(String text, IReaderRow row, MemberMapData memberMapData)\n at CsvHelper.Expressions.RecordCreator.CreateT\n at CsvHelper.Expressions.RecordManager.CreateT\n at CsvHelper.CsvReader.d__87`1.MoveNext()\n at dd_journal.Program.Main(String[] args) in /Users/abhi/Documents/Practice/dd-journal/Program.cs:22
CodePudding user response：
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);
CodePudding user response：
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:
- Update the CSV file to add a default value to the empty fields (i.e. 0 for LensBespokePrice).
- 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 protected],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 theforeachloop within theMainmethod 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.