CODEWITHSUNDEEP
c#.netcsvhelper
Mhadipor
Mhadipor

Reputation: 53

CsvHealper - How to ignore empty columns in csv

New to csvHelper. I'd like to delete empty columns from csv file. I'm using CsvWriter to write the csv row definition class to stream.

Here is my code:

var records = getRecords(); // list of row definition items
var stream = new MemoryStream();
var writer = new StreamWriter(stream);
using (var csv = new CsvWriter(writer))
{
    csv.WriteRecords(records);
    writer.Flush();
    return stream;
}

Is there any way to make CsvWriter ignore empty columns? maybe configuration?

actual csv: 

First Name | Middle Name| Last Name |Gender
FName1     |            | LName1    | Male
FName2     |            | LName2    | Female
FName3     |            | LName3    | 


expected csv:

First Name | Last Name |Gender
FName1     | LName1    | Male
FName2     | LName2    | Female
FName3     | LName3    |

Upvotes: 3

Views: 2785

Answers (3)

adam.k
adam.k

Reputation: 404

A more generic solution would be to convert your records into a List<dynamic> and use reflection to build each element in that list. Then examine what columns are used in this list (what props occur in it), create a second list in which every used property is filled with a value, null if no value. Then dump that list into csv. A little convoluted but works :)

var firstSort = new List<dynamic>();
var secondSort = new List<dynamic>();

//firstly convert data into dynamic objects
foreach (var rec in records)
{
     dynamic dRec = new ExpandoObject();
     var dataDictionary = (IDictionary<string, object>)dRec;

     //add properties
     foreach (var propertyInfo in rec.GetType().GetProperties())
     {
         var propertyName = propertyInfo.Name;
         var propertyValue = propertyInfo.GetValue(rec);
         dataDictionary.Add(propertyName, propertyValue);
     }

     firstSort.Add(dRec)
}

//get all resulting columns
var columns = new HashSet<string>();
firstSort.ForEach(r => columns.UnionWith((r as IDictionary<string, object>).Keys));

//fill all props (columns) for every row
foreach (IDictionary<string, object> rOld in firstSort)
{
    var rNewDynamic = (IDictionary<string, object>)(new ExpandoObject());

    foreach (var c in columns)
    {
        if (!rOld.ContainsKey(c))
            rNewDynamic.Add(c, null);
        else
            rNewDynamic[c] = rOld[c];
    }

    secondSort.Add(rNewDynamic);
}

//dump the secondSort list into the csv
csvWriter.WriteRecords(secondSort);

That assures each property value ends up in proper column for every row.

Upvotes: 0

David Specht
David Specht

Reputation: 9094

It is possible to use configuration to accomplish this.

public static void Main(string[] args)
{
    var records = new List<Foo>
    {
        new Foo { FirstName = "FName1", LastName = "LName1", Gender = "Male"},
        new Foo { FirstName = "FName2", LastName = "LName2", Gender = "Female"},
        new Foo { FirstName = "FName3", LastName = "LName3"},
    };

    using (var csv = new CsvWriter(Console.Out))
    {
        var map = new FooMap();

        var properties = typeof(Foo).GetProperties();

        foreach (var property in properties)
        {
            if(records.All(foo => property.GetValue(foo) == null))
            {
                map.Map(typeof(Foo), property).Ignore();
            }
        }

        csv.Configuration.RegisterClassMap(map);

        csv.WriteRecords(records);
    }

    Console.ReadKey();
}

public class Foo
{
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string Gender { get; set; }
}

public class FooMap : ClassMap<Foo>
{
    public FooMap()
    {
        Map(m => m.FirstName).Name("First Name");
        Map(m => m.MiddleName).Name("Middle Name");
        Map(m => m.LastName).Name("Last Name");
        Map(m => m.Gender);
    }
}

And if you didn't want to create a separate class map, just replace FooMap with DefaultClassMap<Foo> and use AutoMap.

var map = new DefaultClassMap<Foo>();
map.AutoMap();

Upvotes: 4

Dave Barnett
Dave Barnett

Reputation: 2226

Project records to a list of objects that do not have the middle name property. Something like this (using linq)

   var projectedRecords = records.Select(r => new PersonDto
   {
       FirstName = r.FirstName,
       LastName = r.LastName,
       Gender = r.Gender
    }).ToList();

Then pass projectedRecords to the csvWriter.

Upvotes: 0

Related Questions