FileHelpers and CSV: what to do when a record can expand unbounded, horizontally

Question

I'm trying to parse this type of CSV file with FileHelpers:

Tom,1,2,3,4,5,6,7,8,9,10
Steve,1,2,3
Bob,1,2,3,4,5,6
Cthulhu,1,2,3,4,5
Greg,1,2,3,4,5,6,7,8,9,10,11,12,13,14

I can't figure out how to parse this with FileHelpers. I would imagine I should be able to do something like this:

[DelimitedRecord(",")]
public class MyRecord
{
    public string Name;

    public List<int> Values;
}

But that doesn't appear to be possible with FileHelpers. The best I can seem to do is this:

[DelimitedRecord(",")]
public class MyRecord
{
    public string Name;

    public string Values;

    public string[] ActualValuesInNiceArray
    {
        get { return Values.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries); }
    }
}

I then would need to split Values on commas to get the set of values for each record. Doesn't seem to be much of a point in using FileHelpers if I have to manually parse a portion of each record.

Am I missing something? I've gone over docs/examples, but can't seem to find a solution for my format. Excel has no trouble with my format, so I would imagine there is a way to do it with an existing free library (FileHelpers or some other library). Any ideas?

Pantaloon

Answer 1

You can use an Array Field and the library will do the work:

[DelimitedRecord(",")]
public class MyRecord
{
    public string Name;

    public int[] Values;
}

You can even use [FieldArrayLength(2, 8)]

[DelimitedRecord(",")]
public class MyRecord
{
    public string Name;

    [FieldArrayLength(2, 8)]
    public int[] Values;
}

The set the min/max number of values

I strongly recomend to download the last version of the library from here:

http://teamcity.codebetter.com/viewType.html?buildTypeId=bt65&tab=buildTypeStatusDiv

Check the artifacts section

Lickerish

This is a little known feature, you are right that we need to add them to the docs and examples, any suggestion about this feature is welcome, Best Regards – Lickerish

Ok - but, what if Values is an array of strings? If I try that, I get this error message: "The field: 'Values' of type: String[] is a non system type, so this field need a CustomConverter (see the docs for more info." – Pantaloon

I just check it with the last version and works perfect for strings, can u send me or post here the record class code. Thanks – Lickerish

It looks exactly the same as your first code block in your answer; just replace public int[] Values with public string[] Values... everything else is identical. I'm referencing FileHelpers.dll v2.0.0.0. – Pantaloon

Also - it failed for ints, too, on my system. I got a similar error message: "The field: Values of type Int32 is a non system type, so this field needs a CustomConverter". – Pantaloon

This is the URL I downloaded it from: sourceforge.net/projects/filehelpers/files/… – Pantaloon

Try with this version teamcity.codebetter.com/repository/download/bt65/9032:id/… – Lickerish

Very clean solution Marcos! I wonder if it could be extended to handle Name-Value pairs sitting within a delimited record? I've seen examples like 'DATE,TYPE,NAME,VALUE,NAME,VALUE,NAME,VALUE' where the pairs need to end up in an array. Admittedly a cleaner approach is the MasterDetail way, but only where we have control of the schema. – Trent

Answer 2

You could create a class MyRecord that holds all the potential values, e.g.

[DelimitedRecord(",")]
public class MyRecord
{
    public string Name;
    public int Value1;
    .....
    [FieldOptional]
    public int Value5;
    ......
    [FieldOptional]
    [FieldNullValue(typeof(int), "-1" )]
    public int Value14;
}

and make most of those fields optional (no. 5 through 14 in my example) and combine that with e.g. a FieldNullValue to handle those non-existing fields (or make those optional fields a nullable int:

 public int? Value5

Darned

Yeah, I was thinking about that, but it just feels odd to have to do it that way. I doubt my data would expand to more than 50 columns, but I would hate for someone to try and use my code with 51 columns, and I only have option fields for the first 50... :-/ – Pantaloon

@unforgiven3: yes, you're absolutely right. See the other answer by the FileHelpers author - if anyone know, he does :-) – Darned