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

No,I didn't try a override..can you show me an example of how overriding helps to attain this? — PassionateProgrammer, Sep 05 '14 at 10:07
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](http://stackoverflow.com/questions/1684667/writing-a-csv-file-in-net). — CodeCaster, Sep 05 '14 at 10:07
It was not contrary to my title, I just said that it was the solution I got through searching here in SO — PassionateProgrammer, Sep 05 '14 at 10:08
Read my comment again. Why do you explicitly ask for "the fastest way"? Aren't you just looking for "any way that works"? — CodeCaster, Sep 05 '14 at 10:09
I asked for the fastest because In some cases I have too many objects in the List — PassionateProgrammer, Sep 05 '14 at 10:12
If I was looking for any way that works I would have gone for string building with foreach... — PassionateProgrammer, Sep 05 '14 at 10:20
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. — CodeCaster, Sep 05 '14 at 10:22
Possible duplicate of [CSV parser/reader for C#?](http://stackoverflow.com/questions/906841/csv-parser-reader-for-c) — , Apr 04 '16 at 08:50

unreal · Accepted Answer · 2019-07-23T09:18:12.997

54

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!

edited Jul 23 '19 at 09:18

answered Sep 05 '14 at 10:49

unreal

1,202
12
17

1

Thanks that takes care of the newline character :) – PassionateProgrammer Sep 05 '14 at 11:09
What exactly are the quotas there? "20 Different Types in JSON, JSV and CSV Serializers *" what does it mean? – Toolkit Mar 27 '16 at 10:28
20 different classes. but if you are using the v3, then there is no limit. – unreal Apr 04 '16 at 13:59

score 21 · Answer 2 · answered Sep 05 '14 at 21:32

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

Thanks a lot for this analysis. – PassionateProgrammer Sep 10 '14 at 05:06 — PassionateProgrammer, Sep 10 '14 at 05:06

score 6 · Answer 3 · answered Sep 05 '14 at 10:39

6

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

answered Sep 05 '14 at 10:39

AVee

3,156
16
17

1

Thank you too for suggesting a nice library. – PassionateProgrammer Sep 05 '14 at 11:13

Cinchoo · Answer 4 · 2021-11-06T14:12:01.800

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

score 2 · Answer 5 · edited Oct 19 '21 at 13:24

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.

score 0 · Answer 6 · answered Mar 24 '18 at 16:59

0

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

answered Mar 24 '18 at 16:59

maeneak

563
6
10

score 0 · Answer 7 · edited Nov 10 '21 at 05:36

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.

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

7 Answers7

Linked

Related