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?

Airily

Are you able to convert it? Which one did you tried? – Leek

Does this answer your question? Fastest way to convert a list of objects to csv with each object values in a new line – Nyeman

Answer 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.

Leishaleishmania

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

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

Forethoughtful

Answer 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.

Mullens

Answer 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.

Anastasius

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

Amii

Answer 6

Putting together comma-separated values I always like to point at the much underrated string.Join(string separator, string[] elements) static method, but if there are helper libraries out there, that's probably better stuff.

Resile

Answer 7

I'd implement some addtional serialzation behaviors as per this article. If you want to get fancy, you can create a setting in your projects properties. This setting would determine if your class used csv or default serialialization. You'd then access it through the techniques shown here. Consider using a static constructor to read appsettings and make a boolean value accessable to your serialization code. Vlads' code looks great, just plug it in to your code. Also you can consider other, maybe more desirable ways to change your serialzation behavior.

Or Create an interface called 'SerializeAsCSV' and use it kind of like this:

// Partial Contents of MyCoolClass.csv:

   public class MyCoolClass : ISerializeAsCSV, IDisposable
   {

      protected static bool serializesToCSV = false;


      static MyCoolClass()
      {
         serializesToCSV = 
            (typeof(MyCoolClass).GetInterface("GrooveySoft.Shared.Interfaces.ISerializeAsCSV") == null)
            ? false
            : true;

      }


      public MyCoolClass(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context)
      {
        // your stuff here
      }

      public void GetObjectData(System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context)
      {
         // your stuff here
      }

    }

// contents of ISerializeAsCSV.cs

using System.Runtime.Serialization;

namespace GrooveySoft.Shared.Interfaces
{
   /// <summary>
   /// indicates that implementor will serialize to csv format
   /// </summary>
   public interface ISerializeAsCSV : ISerializable
   {
   }
}

This should get your started . . . I haven't compiled and tested this . . but you get the general idea.

Terena

Answer 8

Another option is Linq to CSV, a package available from NuGet. See Combine contents of two files using LINQ to CSV for example usage.

Thirst