Create a CSV File from a .NET Generic List

Here's a method I've been working on for a recent project. It will make a csv file from a generic list of type <T>.

There are methods out there for doing similar with DataTables... I like working with generic lists myself.

It does what it needs to do, but I'm not terribly happy with it yet, especially with a class with multiple constructors (all properties are used in csv).

(sorry about the formatting... gotta find a c# code format plugin...)
UPDATE: http://drupal.org/project/geshifilter

/// <summary>
/// Creates the CSV from a generic list.
/// </summary>;
/// <typeparam name="T"></typeparam>;
/// <param name="list">The list.</param>;
/// <param name="csvNameWithExt">Name of CSV (w/ path) w/ file ext.</param>;
        public static void CreateCSVFromGenericList<T>(List<T> list, string csvNameWithExt)
        {
            if (list == null || list.Count == 0) return;
 
                //get type from 0th member
                Type t = list[0].GetType();
                string newLine = Environment.NewLine;
 
                using (var sw = new StreamWriter(csvNameWithExt))
                {
                    //make a new instance of the class name we figured out to get its props
                    object o = Activator.CreateInstance(t);
                    //gets all properties
                    PropertyInfo[] props = o.GetType().GetProperties();
 
                    //foreach of the properties in class above, write out properties
                    //this is the header row
                    foreach (PropertyInfo pi in props)
                    {
                        sw.Write(pi.Name.ToUpper() + ",");
                    }
                    sw.Write(newLine);
 
                    //this acts as datarow
                    foreach (T item in list)
                    {
                        //this acts as datacolumn
                        foreach (PropertyInfo pi in props)
                        {
		  //this is the row+col intersection (the value)
                            string whatToWrite =
                                Convert.ToString(item.GetType()
                                                     .GetProperty(pi.Name)
                                                     .GetValue(item, null))
                                    .Replace(',', ' ') + ',';
 
                            sw.Write(whatToWrite);
 
                        }
                        sw.Write(newLine);
                    }
                }  
        }

So there you have it... C# generic list to csv file!

Couple things to change/ideas...

  • take in a delimiter (string) (comma is standard now, but it could be anything, use that as .Replace() of value as well)
  • remove last comma in header row.
  • fix this line 'Type t = list[0].GetType();' (I don't like it!)

Any suggestions are welcome!

kick it on DotNetKicks.com

Tags:
brian's picture
eek

@Ricky. If you're making a CSV, i would do some preprocessing to use a different separator.

Submitted by brian on Sun, 06/24/2012 - 3:45pm.
thanks

thanks

Submitted by Anonymous (not verified) on Thu, 05/10/2012 - 9:11am.
How to Handle if my column

How to Handle if my column contains comma in its name Here is an example : If my Vendor Name is Bill, Gates but how to handle this issue ?

regards,

Ricky

Submitted by Anonymous (not verified) on Thu, 12/15/2011 - 1:05pm.
changed CreateCSVFromGenericList to be used with dynamics

Hi,

I've changed your code a little bit, 'cause it had some problems when using List.
Good luck,

Matthijs

///
/// Creates the CSV from a generic list.
/// ;
/// ;
/// The list.;
/// Name of CSV (w/ path) w/ file ext.;
public static void CreateCSVFromGenericList(this List list, string csvNameWithExt)
{
//todo: check for values that start with 0
if (list == null || list.Count == 0) return;

//get type from 0th member
var firstResult = (from a in list select a).FirstOrDefault();
Type t = firstResult.GetType();

string newLine = Environment.NewLine;

using (var sw = new StreamWriter(csvNameWithExt))
{
//Get first result of the type to figure out its props
if (firstResult != null)
{

//gets all properties
PropertyInfo[] props = firstResult.GetType().GetProperties();

//foreach of the properties in class above, write out properties
//this is the header row
foreach (PropertyInfo pi in props)
{
sw.Write(pi.Name.ToUpper() + ",");
}
sw.Write(newLine);

//this acts as datarow
foreach (T item in list)
{
//this acts as datacolumn
foreach (PropertyInfo pi in props)
{
//this is the row+col intersection (the value)
string whatToWrite =
Convert.ToString(item.GetType()
.GetProperty(pi.Name)
.GetValue(item, null))
.Replace(',', ' ') + ',';

sw.Write(whatToWrite);

}
sw.Write(newLine);
}
}
}
}

Submitted by Matthijs de Zwart (not verified) on Thu, 11/03/2011 - 9:19am.
Quotes around fields that include newlines

Thanks Kevin, I like your implementation. I made a small change to check for "\n" (newlines) as well. Below is the modified version:

///
/// Method to write a delimited file, given a generic enumerable.
///
/// The type T
/// an enumerable of type T
/// The destination file info
/// The delimiter ("," for example)
public static void WriteDelimitedFile(IEnumerable list, FileInfo saveFile, string delimiter)
{
if (list == null) return;
using (StreamWriter sw = saveFile.CreateText())
{
PropertyInfo[] props = typeof(T).GetProperties();
var headerNames = props.Select(x => x.Name);
sw.WriteLine(string.Join(delimiter, headerNames.ToArray()));
foreach (T item in list)
{
T item1 = item; // to prevent access to modified closure
var values = props
.Select(x => x.GetValue(item1, null) ?? "") // the null coalescing operator, replace null with ""
.Select(x => x.ToString())
.Select(x => x.Contains(delimiter) || x.Contains("\n") ? "\"" + x + "\"" : x); // if a value contains the delimiter, surround with quotes
sw.WriteLine(string.Join(delimiter, values.ToArray()));
}
sw.Close();
}
}

Submitted by Jaco Swarts (not verified) on Sun, 09/25/2011 - 7:24pm.
Extension Method

Here is an extension method I made based on your code.

-------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Globalization;
using System.IO;
using System.Reflection;

public static class Extension
{
public static string BuildCsvString(this List myList)
{
if (myList != null && myList.Count > 0)
{
//Csv string
StringWriter mySw = new StringWriter();

//Add headers by looping properties of type
PropertyInfo[] properties = myList[0].GetType().GetProperties();
foreach (PropertyInfo curProp in properties)
{
mySw.Write('"' + curProp.Name.ToUpper() + '"' + ",");
}
mySw.Write('\n');

//Loop items in list (rows)
object curValue;
foreach (T curItem in myList)
{
//Loop properties (columns)
foreach (PropertyInfo curProp in properties)
{
//Get value, check, and write
curValue = curItem.GetType().GetProperty(curProp.Name).GetValue(curItem, null);
if(curValue != null)
{
mySw.Write('"' + curValue.ToString() + '"' + ",");
}
else
{
mySw.Write('"' + "" + '"' + ",");
}

}
mySw.Write('\n');
}

//Clean up and return
mySw.Close();
return mySw.ToString();
}
else
{
return "";
}
}
}

Submitted by djdmbrwsk (not verified) on Tue, 09/13/2011 - 3:20pm.
Extenders

Hello,

Thanks for this code, I have modified it slightly and made it into an extender for any enumerable object:

public static string ToCSV(this IEnumerable collection)
{
if (collection == null || collection.Count() == 0) return string.Empty;

//get type from 0th member

string newLine = Environment.NewLine;

var sw = new StringBuilder();

//gets all properties
PropertyInfo[] props = typeof(T).GetProperties();

//foreach of the properties in class above, write out properties
//this is the header row
foreach (PropertyInfo pi in props)
{
sw.Append(pi.Name.ToUpper() + ",");
}
sw.Append(newLine);

//this acts as datarow
foreach (var item in collection)
{
//this acts as datacolumn
foreach (PropertyInfo pi in props)
{
//this is the row+col intersection (the value)
string whatToWrite =
Convert.ToString(item.GetType()
.GetProperty(pi.Name)
.GetValue(item, null))
.Replace(',', ' ') + ',';

sw.Append(whatToWrite);

}
sw.Append(newLine);
}
return sw.ToString();

}

So you can just call your collection.ToCSV() and it will return a CSV string that you can then output to file.

Submitted by Richard Priddy (not verified) on Mon, 06/06/2011 - 4:04pm.
danka

thanks, came in useful. needed a quickie way to save a list and this did it.

Submitted by setiri (not verified) on Fri, 04/15/2011 - 12:13am.
vb version

I know this is a c sharp site but...

Public Shared Sub WriteDelimitedFile(ByVal list As IEnumerable, ByVal saveFile As FileInfo, ByVal delimiter As String)
If list Is Nothing Then Return

Using OutputFile As StreamWriter = saveFile.CreateText()

Dim Props As PropertyInfo() = list(0).[GetType]().GetProperties()

' Output Header.
Dim HeaderNames = Props.Select(Function(x) x.Name)
OutputFile.WriteLine(String.Join(delimiter, HeaderNames.ToArray()))

For Each Item In list
Dim Item1 = Item
' If the value is Nothing, replace with ""
' If a value contains the delimiter, surround with quotes
Dim Values = Props.Select(Function(x) If(x.GetValue(Item1, Nothing), "")).Select(Function(x) x.ToString()).Select(Function(x) If(x.Contains(delimiter), String.Format("""{0}""", x), x))
OutputFile.WriteLine(String.Join(delimiter, Values.ToArray()))
Next

OutputFile.Close()
End Using
End Sub

Submitted by Dave (not verified) on Sat, 03/05/2011 - 5:26pm.
brian's picture
totally

very good point [and nice article]. thanks!

Submitted by brian on Thu, 12/30/2010 - 12:26am.
A Better Way

I don't recommend this approach because it modifies values that contain embedded commas. You can leave such values intact by simply wrapping them with quotes.

I've published a couple of classes to read and write CSV files on Black Belt Coder. Perhaps some of your readers would be interested in seeing my version.

Submitted by Jonathan Wood (not verified) on Sun, 12/19/2010 - 6:06pm.
Thanks for this method, I

Thanks for this method, I started with this and made some changes to use LINQ syntax to avoid the for loops. Here is my implementation (sorry for no formatting)

///
/// Method to write a delimited file, given a generic enumerable.
///
/// The type T
/// an enumerable of type T
/// The destination file info
/// The delimiter ("," for example)
public static void WriteDelimitedFile(IEnumerable list, FileInfo saveFile,string delimiter)
{
if (list == null) return;
using (StreamWriter sw = saveFile.CreateText())
{
PropertyInfo[] props = typeof(T).GetProperties();
var headerNames = props.Select(x => x.Name);
sw.WriteLine(string.Join(delimiter, headerNames.ToArray()));
foreach (T item in list)
{
T item1 = item; // to prevent access to modified closure
var values = props
.Select(x => x.GetValue(item1, null) ?? "") // the null coalescing operator, replace null with ""
.Select(x => x.ToString())
.Select(x => x.Contains(delimiter) ? "\"" + x + "\"" : x); // if a value contains the delimiter, surround with quotes
sw.WriteLine(string.Join(delimiter, values.ToArray()));
}
sw.Close();
}
}

Submitted by kevin (not verified) on Thu, 08/20/2009 - 6:12pm.
That's pretty sweet!

This is really nice to have in my bag of tricks now! Thanks for sharing! It's the kind of thing you never know when you'll need it, but saves the day when it does.

Submitted by Duncan (not verified) on Thu, 12/04/2008 - 11:44am.
brian's picture
hot

thanks!

Submitted by brian on Tue, 12/02/2008 - 2:03pm.
Replace: 'Type t =

Replace: 'Type t = list[0].GetType();' with typeof(T);

Submitted by Anonymous (not verified) on Tue, 12/02/2008 - 6:32am.

About Brian


profile for bluevoodoo1 on Stack Exchange, a network of free, community-driven Q&A sites

Brian Canzanella brings you nifty tips and tricks for most things .NET. read more...

Readers / Stuff