Generic class to CSV (all properties)

Question

Im looking for a way to create CSV from all class instances.

What i want is that i could export ANY class (all of its instances) to CSV.

Can some1 direct me to possible solution for this (in case already anwsered).

thanx !

Iphigenia

So .. just write it? It's a serialisation process, but instead of to XML or Binary it's to CSV. What problem are you having? – Lashley

I can't imagine that this would be possible to do in a sensible way. If you've got an object, containing a dictionary that contains objects that have other collections in them etc, how would you store that in CSV. XML might be possible though, but might get very messy if multiple objects have references to the same data. – Farias

Give (a) an example for a class, and (b) an example for the CSV you expect, and you will get plenty of answers how to go from (a) to (b). – Kruse

silky: i could write it, but i need something generic, as when i am adding properties on classes i wont have to worry about CSV .. Doc: simple data classes like User(name,surname,age...). I was thinking something like ";" delimited including header for starters. – Iphigenia

Yes im aware of that. But i will use the ToCSV on simple classes ... – Iphigenia

for simple classes, the reflection solutions will do it, like the ones in the link Adam gave. – Kruse

possible duplicate of Best practices for serializing objects to a custom string format for use in an output file – Indulgent

Answer 1

Actually, something similar has been addressed here:

Best practices for serializing objects to a custom string format for use in an output file

Is this useful to you?

There is a sample that uses reflection to pull out the field names and values and append them to a string.

Orthochromatic

it looks ok, i never used extension methods :/ I created a static class and i added the methods Per Hejndorf posted in the ost u linked. Now i dont know how to extend the methods to List<>, so i could call .ToCSV on an instance of the list containing instances of lets say "User" class ... – Iphigenia

Answer 2

Have a look at LINQ to CSV. Although it's a little on the heavy side, which is why I wrote the following code to perform just the small subset of functionality that I needed. It handles both properties and fields, like you asked for, although not much else. One thing it does do is properly escape the output in case it contains commas, quotes, or newline characters.

public static class CsvSerializer {
    /// <summary>
    /// Serialize objects to Comma Separated Value (CSV) format [1].
    /// 
    /// Rather than try to serialize arbitrarily complex types with this
    /// function, it is better, given type A, to specify a new type, A'.
    /// Have the constructor of A' accept an object of type A, then assign
    /// the relevant values to appropriately named fields or properties on
    /// the A' object.
    /// 
    /// [1] http://tools.ietf.org/html/rfc4180
    /// </summary>
    public static void Serialize<T>(TextWriter output, IEnumerable<T> objects) {
        var fields =
            from mi in typeof (T).GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static)
            where new [] { MemberTypes.Field, MemberTypes.Property }.Contains(mi.MemberType)
            let orderAttr = (ColumnOrderAttribute) Attribute.GetCustomAttribute(mi, typeof (ColumnOrderAttribute))
            orderby orderAttr == null ? int.MaxValue : orderAttr.Order, mi.Name
            select mi;
        output.WriteLine(QuoteRecord(fields.Select(f => f.Name)));
        foreach (var record in objects) {
            output.WriteLine(QuoteRecord(FormatObject(fields, record)));
        }
    }

    static IEnumerable<string> FormatObject<T>(IEnumerable<MemberInfo> fields, T record) {
        foreach (var field in fields) {
            if (field is FieldInfo) {
                var fi = (FieldInfo) field;
                yield return Convert.ToString(fi.GetValue(record));
            } else if (field is PropertyInfo) {
                var pi = (PropertyInfo) field;
                yield return Convert.ToString(pi.GetValue(record, null));
            } else {
                throw new Exception("Unhandled case.");
            }
        }
    }

    const string CsvSeparator = ",";

    static string QuoteRecord(IEnumerable<string> record) {
        return String.Join(CsvSeparator, record.Select(field => QuoteField(field)).ToArray());
    }

    static string QuoteField(string field) {
        if (String.IsNullOrEmpty(field)) {
            return "\"\"";
        } else if (field.Contains(CsvSeparator) || field.Contains("\"") || field.Contains("\r") || field.Contains("\n")) {
            return String.Format("\"{0}\"", field.Replace("\"", "\"\""));
        } else {
            return field;
        }
    }

    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class ColumnOrderAttribute : Attribute {
        public int Order { get; private set; }
        public ColumnOrderAttribute(int order) { Order = order; }
    }
}

Paginal

Answer 3

You can use reflection to traverse all the class properties/fields and write them to CSV. A better approach would be to define a custom attribute and decorate the members you want to export and only export those attributes.

Fusty

Answer 4

I am separating my answer into two sections: The first one is how to export some generic item list into csv, with encoding, headers - (it will build csv data only for specified headers, and will ignore unneeded properties).

public string ExportCsv<T>(IEnumerable<T> items, Dictionary<string, string> headers)
{
    string result;
    using (TextWriter textWriter = new StreamWriter(myStream, myEncoding))
    {
        result = this.WriteDataAsCsvWriter<T>(items, textWriter, headers);
    }
    return result;
}

private string WriteDataAsCsvWriter<T>(IEnumerable<T> items, TextWriter textWriter, Dictionary<string, string> headers)
{
    //Add null validation

    ////print the columns headers
    StringBuilder sb = new StringBuilder();

    //Headers
    foreach (KeyValuePair<string, string> kvp in headers)
    {
        sb.Append(ToCsv(kvp.Value));
        sb.Append(",");
    }
    sb.Remove(sb.Length - 1, 1);//the last ','
    sb.Append(Environment.NewLine);

    //the values
    foreach (var item in items)
    {
        try
        {
            Dictionary<string, string> values = GetPropertiesValues(item, headers);

            foreach (var value in values)
            {
                sb.Append(ToCsv(value.Value));
                sb.Append(",");
            }
            sb.Remove(sb.Length - 1, 1);//the last ','
            sb.Append(Environment.NewLine);
        }
        catch (Exception e1)
        {
             //do something
        }
    }
    textWriter.Write(sb.ToString());

    return sb.ToString();
}

//Help function that encode text to csv:
public static string ToCsv(string input)
{
    if (input != null)
    {
        input = input.Replace("\r\n", string.Empty)
            .Replace("\r", string.Empty)
            .Replace("\n", string.Empty);
        if (input.Contains("\""))
        {
            input = input.Replace("\"", "\"\"");
        }

        input = "\"" + input + "\"";
    }

    return input;
}

This is the most important function, Its extracting the properties values out of (almost) any generic class.

private Dictionary<string, string> GetPropertiesValues(object item, Dictionary<string, string> headers)
{
    Dictionary<string, string> values = new Dictionary<string, string>();
    if (item == null)
    {
        return values;
    }

    //We need to make sure each value is coordinated with the headers, empty string 
    foreach (var key in headers.Keys)
    {
        values[key] = String.Empty;
    }

    Type t = item.GetType();
    PropertyInfo[] propertiesInfo = t.GetProperties();

    foreach (PropertyInfo propertiyInfo in propertiesInfo)
    {
        //it not complex: string, int, bool, Enum
        if ((propertiyInfo.PropertyType.Module.ScopeName == "CommonLanguageRuntimeLibrary") || propertiyInfo.PropertyType.IsEnum)
        {
            if (headers.ContainsKey(propertiyInfo.Name))
            {
                var value = propertiyInfo.GetValue(item, null);
                if (value != null)
                {
                    values[propertiyInfo.Name] = value.ToString();
                }                         
            }
        }
        else//It's complex property
        {
            if (propertiyInfo.GetIndexParameters().Length == 0)
            {
                Dictionary<string, string> lst = GetPropertiesValues(propertiyInfo.GetValue(item, null), headers);
                foreach (var value in lst)
                {
                    if (!string.IsNullOrEmpty(value.Value))
                    {
                        values[value.Key] = value.Value;
                    }
                }
            }
        }
    }
    return values;
}

Example for GetPropertiesValues:

public MyClass 
{
    public string Name {get; set;}
    public MyEnum Type {get; set;}
    public MyClass2 Child {get; set;}
}
public MyClass2
{
    public int Age {get; set;}
    public DateTime MyDate {get; set;}
}

MyClass myClass = new MyClass()
{
    Name = "Bruce",
    Type = MyEnum.Sometype,
    Child = new MyClass2()
    {
        Age = 18,
        MyDate = DateTime.Now()
    }
};

Dictionary<string, string> headers = new Dictionary<string, string>();
headers.Add("Name", "CustomCaption_Name");
headers.Add("Type", "CustomCaption_Type");
headers.Add("Age", "CustomCaption_Age");

GetPropertiesValues(myClass, headers)); // OUTPUT: {{"Name","Bruce"},{"Type","Sometype"},{"Age","18"}}

Chesnut

Answer 5

My answer is based on Michael Kropat's answer from above.

I added two functions to his answer because it didn't want to write straight to file as I still had some further processing to do. Instead I wanted the header information separate to the values so I could put everything back together later.

    public static string ToCsvString<T>(T obj)
    {
        var fields =
            from mi in typeof(T).GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static)
            where new[] { MemberTypes.Field, MemberTypes.Property }.Contains(mi.MemberType)
            let orderAttr = (ColumnOrderAttribute)Attribute.GetCustomAttribute(mi, typeof(ColumnOrderAttribute))
            select mi;

        return QuoteRecord(FormatObject(fields, obj));
    }

    public static string GetCsvHeader<T>(T obj)
    {
        var fields =
            from mi in typeof(T).GetMembers(BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static)
            where new[] { MemberTypes.Field, MemberTypes.Property }.Contains(mi.MemberType)
            let orderAttr = (ColumnOrderAttribute)Attribute.GetCustomAttribute(mi, typeof(ColumnOrderAttribute))
            select mi;

        return QuoteRecord(fields.Select(f => f.Name));
    }

Martingale