How can I convert a list of objects to csv?

Question

If I have a list of objects called "Car":

public class Car
{
     public string Name;
     public int Year;
     public string Model;
}

How do I convert a list of objects, e.g. List<Car> to a csv?

Solution 1

1. FileHelpers Library
2. Text OleDb Provider
3. Manual via String concatenation according to RFC-4180
4. Third party library, for example Aspose.Cells can do it without any friction from you. And it is very fast.

Solution 2

add the following method to Car:

String Escape(String s)
{
    StringBuilder sb = new StringBuilder();
    bool needQuotes = false;
    foreach (char c in s.ToArray())
    {
        switch (c)
        {
            case '"': sb.Append("\\\""); needQuotes = true; break;
            case ' ': sb.Append(" "); needQuotes = true; break;
            case ',': sb.Append(","); needQuotes = true; break;
            case '\t': sb.Append("\\t"); needQuotes = true; break;
            case '\n': sb.Append("\\n"); needQuotes = true; break;
            default: sb.Append(c); break;
        }
    }
    if (needQuotes)
        return "\"" + sb.ToString() + "\"";
    else
        return sb.ToString();
}

public void SerializeAsCsv(Stream stream)
{
    stream.Write(Escape(Name));
    stream.Write(",");
    stream.Write(Year.ToString());
    stream.Write(",");
    stream.Write(Escape(Model));
    stream.Write("\n");
}

Now you can serialize the whole list:

foreach (Car car in list)
{
    car.SerializeAsCsv(stream);
}

Solution 3

Look at the FileHelpers library.

From the site:

The FileHelpers are a free and easy to use .NET library to import/export data from fixed length or delimited records in files, strings or streams.

This will make sure that all kinds of gotchas in line termination, escaping and such are handled correctly, as per RFC-4180.

Solution 4

I was searching for a solution for this, but none of the answers I found satisfied my drive for simplicity. I realised I had the answer already with my auto CRUD code. I repurposed it and came up with the following code:

using System.Reflection;
    /// <summary>
    /// Using a bit of reflection to build up the strings.
    /// </summary>
    public static string ToCsvHeader(this object obj)
    {
        Type type = obj.GetType();
        var properties = type.GetProperties(BindingFlags.DeclaredOnly |
                                       BindingFlags.Public |
                                       BindingFlags.Instance);

        string result = string.Empty;
        Array.ForEach(properties, prop =>
        {
            result += prop.Name + ",";
        });

        return (!string.IsNullOrEmpty(result) ? result.Substring(0, result.Length - 1) : result);
    }

    public static string ToCsvRow(this object obj)
    {
        Type type = obj.GetType();
        var properties = type.GetProperties(BindingFlags.DeclaredOnly |
                                       BindingFlags.Public |
                                       BindingFlags.Instance);

        string result = string.Empty;
        Array.ForEach(properties, prop =>
        {
            var value = prop.GetValue(obj, null);
            var propertyType = prop.PropertyType.FullName;
            if (propertyType == "System.String")
            {
                // wrap value incase of commas
                value = "\"" + value + "\"";
            }

            result += value + ",";

        });

        return (!string.IsNullOrEmpty(result) ? result.Substring(0, result.Length - 1) : result);
    }

This would add an extension method on to every object. Usage like this:

var burgers = new List<Cheeseburger>();
var output = burgers.ToCsvHeader();
output += Environment.NewLine;

burgers.ForEach(burger =>
{
    output += burger.ToCsvRow();
    output += Environment.NewLine;
});
var path = "[where ever you want]";
System.IO.File.WriteAllText(path, output);

There is probably a better way to write these two methods above, but this works perfectly for my situation. Hope it helps someone.

Solution 5

You could simply override the ToString method or create a ToCSVRow() method as such

    public String ToCSVRow()
    {
        return Name + "," + Year.ToString() + "," + Model;
    }

And then just do something like this where needed.

    using (StreamWriter file = new StreamWriter(@"C:\Wherever\yourfilename.txt"))
        {
            foreach (var item in yourlist)
            {
                file.WriteLine(item.ToCSVRow());
            }
        }

View more solutions

Comments

[TrueWill]

+1 for the link to the RFC! There's also a nice reference on Wikipedia: en.wikipedia.org/wiki/Comma-separated_values