Fastest way to convert a list of objects to csv with each object values in a new line

Question

I have a class as follows :

public class Test
{
    public int Id {get;set;}
    public string Name { get; set; }
    public string CreatedDate {get;set;}
    public string DueDate { get; set; } 
    public string ReferenceNo { get; set; }       
    public string Parent { get; set; }
}

and I have a list of Test objects

List<Test>testobjs=new List();

Now I would like to convert it into csv in following format:

"1,John Grisham,9/5/2014,9/5/2014,1356,0\n2,Stephen King,9/3/2014,9/9/2014,1367,0\n3,The Rainmaker,4/9/2014,18/9/2014,1";

I searched for "Converting list to csv c#" and I got solutions as follows:

string.Join(",", list.Select(n => n.ToString()).ToArray())

But this will not put the \n as needed i.e for each object

Is there any fastest way other than string building to do this? Please help...

Ketosis

Have you overriden the default ToString? – Enwreathe

No,I didn't try a override..can you show me an example of how overriding helps to attain this? – Ketosis

Contrary to your title, you're not looking for the fastest way, but for any functioning way. string.Join(",", ...) is not a proper way to write CSV. See Writing a CSV file in .net. – Gynoecium

It was not contrary to my title, I just said that it was the solution I got through searching here in SO – Ketosis

Read my comment again. Why do you explicitly ask for "the fastest way"? Aren't you just looking for "any way that works"? – Gynoecium

I asked for the fastest because In some cases I have too many objects in the List<Test> – Ketosis

If I was looking for any way that works I would have gone for string building with foreach... – Ketosis

I think you're better off using the string building approach and explaining why that doesn't work. The solutions mentioned here will most likely cause the same trouble as a StringBuilder. – Gynoecium

Possible duplicate of CSV parser/reader for C#? – Rabideau

Answer 1

Use servicestack.text

Install-Package ServiceStack.Text

and then use the string extension methods ToCsv(T)/FromCsv()

Examples: https://github.com/ServiceStack/ServiceStack.Text

Update: Servicestack.Text is now free also in v4 which used to be commercial. No need to specify the version anymore! Happy serializing!

Tungting

What exactly are the quotas there? "20 Different Types in JSON, JSV and CSV Serializers *" what does it mean? – Phytology

20 different classes. but if you are using the v3, then there is no limit. – Tungting

Project is no longer maintained – Whopper

Answer 2

Because speed was mentioned in the question, my interest was piqued on just what the relative performances might be, and just how fast I could get it.

I know that StringBuilder was excluded, but it still felt like probably the fastest, and StreamWriter has of course the advantage of writing to either a MemoryStream or directly to a file, which makes it versatile.

So I knocked up a quick test.

I built a list half a million objects identical to yours.

Then I serialized with CsvSerializer, and with two hand-rolled tight versions, one using a StreamWriter to a MemoryStream and the other using a StringBuilder.

The hand rolled code was coded to cope with quotes but nothing more sophisticated. This code was pretty tight with the minimum I could manage of intermediate strings, no concatenation... but not production and certainly no points for style or flexibility.

But the output was identical in all three methods.

The timings were interesting:

Serializing half a million objects, five runs with each method, all times to the nearest whole mS:

StringBuilder     703     734     828     671     718   Avge=     730.8
MemoryStream      812     937     874     890     906   Avge=     883.8
CsvSerializer   1,734   1,469   1,719   1,593   1,578   Avge=   1,618.6

This was on a high end i7 with plenty of RAM.

Other things being equal, I would always use the library.

But if a 2:1 performance difference became critical, or if RAM or other issues turned out to exaggerate the difference on a larger dataset, or if the data were arriving in chunks and was to be sent straight to disk, I might just be tempted...

Just in case anyone's interested, the core of the code (for the StringBuilder version) was

    private void writeProperty(StringBuilder sb, string value, bool first, bool last)
    {
        if (! value.Contains('\"'))
        {
            if (!first)
                sb.Append(',');

            sb.Append(value);

            if (last)
                sb.AppendLine();
        }
        else
        {
            if (!first)
                sb.Append(",\"");
            else
                sb.Append('\"');

            sb.Append(value.Replace("\"", "\"\""));

            if (last)
                sb.AppendLine("\"");
            else
                sb.Append('\"');
        }
    }

    private void writeItem(StringBuilder sb, Test item)
    {
        writeProperty(sb, item.Id.ToString(), true, false);
        writeProperty(sb, item.Name, false, false);
        writeProperty(sb, item.CreatedDate, false, false);
        writeProperty(sb, item.DueDate, false, false);
        writeProperty(sb, item.ReferenceNo, false, false);
        writeProperty(sb, item.Parent, false, true);
    }

Sensillum

Answer 3

If you don't want to load library's than you can create the following method:

private void SaveToCsv<T>(List<T> reportData, string path)
{
    var lines = new List<string>();
    IEnumerable<PropertyDescriptor> props = TypeDescriptor.GetProperties(typeof(T)).OfType<PropertyDescriptor>();
    var header = string.Join(",", props.ToList().Select(x => x.Name));
    lines.Add(header);
    var valueLines = reportData.Select(row => string.Join(",", header.Split(',').Select(a => row.GetType().GetProperty(a).GetValue(row, null))));
    lines.AddRange(valueLines);
    File.WriteAllLines(path, lines.ToArray());
}

and than call the method:

SaveToCsv(testobjs, "C:/PathYouLike/FileYouLike.csv")

Jonna

This was useful for me, but I changed the one line to account for commas or quotes in the original data. It does this by properly escaping quotes and also wrapping the values in quotes. var valueLines = reportData.Select(row => string.Join(",", header.Split(',').Select(a => $"\"{(row.GetType().GetProperty(a).GetValue(row, null)).ToString().Replace("\"", "\"\"")}\""))); – Inexperienced

Answer 4

Your best option would be to use an existing library. It saves you the hassle of figuring it out yourself and it will probably deal with escaping special characters, adding header lines etc. You could use the CSVSerializer from ServiceStack. But there are several other in nuget. Creating the CSV will then be as easy as string csv = CsvSerializer.SerializeToCsv(testobjs);

Dulse

Answer 5

I really like @marks answer. Adding a version of that adding support for escaping ',' in values and more comments

/// <summary>
/// Saves a list of data objects to a CSV file.
/// </summary>
/// <typeparam name="T">The type of the data objects.</typeparam>
/// <param name="reportData">The list of data objects.</param>
/// <returns>A string representation of the CSV file.</returns>
private string SaveToCsv<T>(List<T> reportData)
{
    var lines = new List<string>();

    // Get the properties of the data type
    var props = TypeDescriptor.GetProperties(typeof(T)).OfType<PropertyDescriptor>();

    // Create the header line by joining the property names
    var header = string.Join(",", props.Select(property => property.Name));
    lines.Add(header);

    // Create value lines for each data object
    var valueLines = reportData.Select(row =>
    {
        // Get the property values and enclose them in quotes if they contain a comma
        var values = header.Split(',').Select(propertyName =>
        {
            var propertyValue = row.GetType().GetProperty(propertyName)?.GetValue(row, null);
            var valueString = propertyValue?.ToString();

            // Add quotes if the value contains a comma
            if (valueString?.Contains(',') == true)
                valueString = $"\"{valueString}\"";

            return valueString;
        });

        // Join the values with commas
        var line = string.Join(",", values);
        return line;
    });

    // Add the value lines to the result
    lines.AddRange(valueLines);

    // Join all lines with newline characters
    return string.Join("\n", lines);

}

If you are after the smaller code:

private string SaveToCsv<T>(List<T> reportData)
{
    var props = TypeDescriptor.GetProperties(typeof(T)).OfType<PropertyDescriptor>();
    var header = string.Join(",", props.Select(property => property.Name));
    var lines = new List<string> { header };
    lines.AddRange(reportData.Select(row => string.Join(",", props.Select(property =>
    {
        var value = property.GetValue(row);
        return value != null && value.ToString().Contains(",") ? $"\"{value}\"" : value?.ToString();
    }))));
    return string.Join("\n", lines);
}

Enwrap

Answer 6

Necromancing this one a bit; ran into the exact same scenario as above, went down the road of using FastMember so we didn't have to adjust the code every time we added a property to the class:

[HttpGet]
public FileResult GetCSVOfList()
{
    // Get your list
    IEnumerable<MyObject> myObjects =_service.GetMyObject();

    //Get the type properties
    var myObjectType = TypeAccessor.Create(typeof(MyObject));
    var myObjectProperties = myObjectType.GetMembers().Select(x => x.Name);

    //Set the first row as your property names
    var csvFile = string.Join(',', myObjectProperties);

    foreach(var myObject in myObjects)
    {
        // Use ObjectAccessor in order to maintain column parity
        var currentMyObject = ObjectAccessor.Create(myObject);
        var csvRow = Environment.NewLine;

        foreach (var myObjectProperty in myObjectProperties)
        {
            csvRow += $"{currentMyObject[myObjectProperty]},";
        }

        csvRow.TrimEnd(',');
        csvFile += csvRow;
    }

    return File(Encoding.ASCII.GetBytes(csvFile), "text/csv", "MyObjects.csv");
}

Should yield a CSV with the first row being the names of the fields, and rows following. Now... to read in a csv and create it back into a list of objects...

Note: example is in ASP.NET Core MVC, but should be very similar to .NET framework. Also had considered ServiceStack.Text but the license was not easy to follow.

Dysgenics

Answer 7

Use Cinchoo ETL

Install-Package ChoETL

or

Install-Package ChoETL.NETStandard

Sample shows how to use it

List<Test> list = new List<Test>();

list.Add(new Test { Id = 1, Name = "Tom" });
list.Add(new Test { Id = 2, Name = "Mark" });

using (var w = new ChoCSVWriter<Test>(Console.Out)
    .WithFirstLineHeader()
    )
{
    w.Write(list);
}

Output CSV:

Id,Name,CreatedDate,DueDate,ReferenceNo,Parent
1,Tom,,,,
2,Mark,,,,

For more information, go to github

https://github.com/Cinchoo/ChoETL

Sample fiddle: https://dotnetfiddle.net/M7v7Hi

Ripsaw

Answer 8

You could use the FileHelpers library to convert a List of objects to CSV.

Consider the given object, add the DelimitedRecord Attribute to it.

[DelimitedRecord(",")]
public class Test
{
    public int Id {get;set;}
    public string Name { get; set; }
    public string CreatedDate {get;set;}
    public string DueDate { get; set; } 
    public string ReferenceNo { get; set; }       
    public string Parent { get; set; }
 }

Once the List is populated, (as per question it is testobjs)

var engine = new FileHelperEngine<Test>();
engine.HeaderText = engine.GetFileHeader();
string dirPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\" + ConfigurationManager.AppSettings["MyPath"];
if (!Directory.Exists(dirPath))
{
   Directory.CreateDirectory(dirPath);
}

//File location, where the .csv goes and gets stored.
string filePath = Path.Combine(dirPath, "MyTestFile_" + ".csv");
engine.WriteFile(filePath, testobjs);

This will just do the job for you. I'd been using this to generate data reports for a while until I switched to Python.

PS: Too late to answer but hope this helps somebody.

Lugo

I like this method best – Contractile

Answer 9

For the best solution, you can read this article: Convert List of Object to CSV File C# - Codingvila

using Codingvila.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
 
namespace Codingvila.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            CodingvilaEntities entities = new CodingvilaEntities();
            var lstStudents = (from Student in entities.Students
                                        select Student);
            return View(lstStudents);
        }
 
        [HttpPost]
        public FileResult ExportToCSV()
        {
            #region Get list of Students from Database
 
            CodingvilaEntities entities = new CodingvilaEntities();
            List<object> lstStudents = (from Student in entities.Students.ToList()
                                        select new[] { Student.RollNo.ToString(),
                                                                Student.EnrollmentNo,
                                                                Student.Name,
                                                                Student.Branch,
                                                                Student.University
                                  }).ToList<object>();
 
            #endregion 
 
            #region Create Name of Columns
 
            var names = typeof(Student).GetProperties()
                        .Select(property => property.Name)
                        .ToArray();
 
            lstStudents.Insert(0, names.Where(x => x != names[0]).ToArray());
 
            #endregion
 
            #region Generate CSV
 
            StringBuilder sb = new StringBuilder();
            foreach (var item in lstStudents)
            {
                string[] arrStudents = (string[])item;
                foreach (var data in arrStudents)
                {
                    //Append data with comma(,) separator.
                    sb.Append(data + ',');
                }
                //Append new line character.
                sb.Append("\r\n");
            }
 
            #endregion
 
            #region Download CSV
 
            return File(Encoding.ASCII.GetBytes(sb.ToString()), "text/csv", "Students.csv");
 
            #endregion
        }
    }
}

Oxide

Answer 10

LINQtoCSV is the fastest and lightest I've found and is available on GitHub. Lets you specify options via property attributes.

Limnetic

Answer 11

I wrote generic method to export a collection to csv rows. It is fast and allows me to manipulate csv's format.

public static class DataHelper {
    public static IEnumerable<string> ExportToCsv<T>(this IEnumerable<T> source) {
        var properties = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.Instance);
        var separator = ",";
        var header = string.Join(separator, properties.Select(x => x.Name));
        var item = Expression.Parameter(typeof(T), "item");
        var formatMethod = typeof(string).GetMethod("Format", new[] { typeof(string), typeof(object[]) });
        Func<PropertyInfo, int, string> getCellFormat = (p, i) => {
            if(p.PropertyType.IsClass)
                return $"\"{{{i}}}\"";
            else if (p.PropertyType == typeof(DateTime))
                return $"{{{i}:s}}";
            //else if (p.PropertyType == typeof(float)
            //    || p.PropertyType == typeof(double)
            //    || p.PropertyType == typeof(decimal))
            //    return $"{{{i}:#.00}}";
            else
                return $"{{{i}}}";
        };
        var rowFormat = Expression.Constant(string.Join(separator, properties.Select(getCellFormat)));
        var values = properties.Select(p => Expression.Convert(Expression.Property(item, p), typeof(object)));
        Func<T, string> toRow = Expression.Lambda<Func<T, string>>(
            Expression.Call(formatMethod, rowFormat, Expression.NewArrayInit(typeof(object), values)), item
            ).Compile();
        
        yield return header;
        foreach(var i in source)
            yield return toRow(i);
    }
}

Usage:

System.IO.File.WriteAllLines("myfilePath.csv", myCollection.ExportToCsv());

Pedro