Can a CSV exported from Excel being deserialized?

ServiceStack.Text CSV
1

I try to deserialize a CSV file that was exported from excel, but with no luck. Is there any documentation WHAT structure is expected?
I have a class like so:

public class ImportedProduct
{
    public DateTime PriceDate { get; set; }
    public string PriceCurrency { get; set; }
    public decimal Price { get; set; }
    public bool IsVatIncluded { get; set; }
    public string Comment { get; set; }
    public string ProductNumber { get; set; }
    public string ProductCode { get; set; }
    public string ProductName { get; set; }
    public string ProductDescription { get; set; }
    public int ProductType { get; set; }
    public int PostingAccountNumber { get; set; }
    public string VatCode { get; set; }
    public string ProductUnit { get; set; }
    public bool AllowFractions { get; set; }
}

This is an extract of the CSV file:

PriceDate,PriceCurrency,Price,IsVatIncluded,Comment,PriceSetBy,VersionNumber,ProductNumber,ProductCode,ProductName,ProductDescription,ProductType,PostingAccountNumber,VatCode,ProductUnit,AllowFractions,
01.09.2017,CHF,63,true,InitialPrice,100100,OliveBlack,Olive nere appassite,Oliven schwarz getrocknet,1,3001,UStr,kg,yes
01.09.2017,CHF,58.9,true,InitialPrice,100101,OliveRed,Olive rosse,Oliven farbig mit Kräuter,1,3001,UStr,kg,yes
01.09.2017,CHF,62,true,InitialPrice,100102,OliveBig,Olive giganti,Oliven grĂźn gross,1,3001,UStr,kg,yes

It means that:

  1. The file was exported using UTF-8
  2. The file has a header line
  3. The file has dates, decimals, strings with german umlauts and french accents etc.
  4. I may contain quoted strings like ,“My string here”,

I tried to de-serialize with:

CsvConfig.ItemDelimiterString = ",";
using (var sr = new StreamReader(FqImportFileName))
{
      var p = CsvSerializer.DeserializeFromReader<ImportedProduct>(sr);
}

Unfortunately this does not work and I get the following exception:
System.FormatException: Input string was not in a correct format.
bei ServiceStack.Text.Support.StringSegmentExtensions.ParseBoolean(StringSegment value)
bei ServiceStack.Text.Common.DeserializeBuiltin1.<>c.<GetParseStringSegmentFn>b__7_0(StringSegment value) bei ServiceStack.Text.Common.JsReader1.<>c__DisplayClass4_01.<GetCoreParseStringSegmentFn>b__4(StringSegment value) bei ServiceStack.Text.Jsv.JsvReader.<>c__DisplayClass2_0.<GetParseFn>b__0(String v) bei ServiceStack.Text.CsvReader1.Read(List1 rows) bei ServiceStack.Text.CsvReader1.ReadRow(String value)
bei ServiceStack.Text.CsvSerializer1.ReadSelf(String value) bei ServiceStack.Text.CsvSerializer1.ReadObject(String value)
bei ServiceStack.Text.CsvSerializer.DeserializeFromString[T](String text)
bei ServiceStack.Text.CsvSerializer.DeserializeFromReader[T](TextReader reader)

Additional questions:

  1. Dates can be passed in many ways, is there any way to annotate or define the format that is expected?
  2. Does DeserializeFromReader<T> return a List<T> or should I loop myself through the file and use DeserializeFromString for every line?
  3. What about expected formats for numbers?

Thanks if you could point me to some additional information how to successfully parse such a CSV file.
Tom

2

The Exception says it contains an invalid Boolean value, looking at the sample data you’re trying to deserialize ‘yes’ which is not a valid Boolean. Use a string property for data that can’t be converted into .NET types.

3

Hmm corrected this but still not working.

System.ArgumentOutOfRangeException: Der Index lag außerhalb des Bereichs. Er darf nicht negativ und kleiner als die Sammlung sein.
Parametername: index
bei System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
bei ServiceStack.Text.CsvReader1.Read(List1 rows)
bei ServiceStack.Text.CsvReader1.ReadRow(String value) bei ServiceStack.Text.CsvSerializer1.ReadSelf(String value)
bei ServiceStack.Text.CsvSerializer`1.ReadObject(String value)
bei ServiceStack.Text.CsvSerializer.DeserializeFromString[T](String text)
bei ServiceStack.Text.CsvSerializer.DeserializeFromReader[T](TextReader reader)

I still do not understand, if the parsing can somehow be configured. From your message I guess that a header is not allowed. Does the last element require the delimiter at the end? What about date format? I have users with English, Italian, German or French culture settings. All have different date formats!

4

CSV isn’t configurable on its own, it uses the built-in derializers in the ServiceStack.Text serializer which can be customized using the JsConfig, e.g. JsConfig<DateTime>.DeserializeFn. But like I said, if the data doesn’t map cleanly to .NET types change to use string properties then do your own custom parsing to parse the string value.

5

Ok, thanks. Solved it with filehelpers.net. This library is probably better suited for my specific usecase where users compose files and I need a highly configurable environement.

1 Like