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 !

Answer 1

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; }
    }
}

Answer 2

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.

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.

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"}}

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));
    }