Converting a generic list to a CSV string

Question

I have a list of integer values (List) and would like to generate a string of comma delimited values. That is all items in the list output to a single comma delimted list.

My thoughts... 1. pass the list to a method. 2. Use stringbuilder to iterate the list and append commas 3. Test the last character and if it's a comma, delete it.

What are your thoughts? Is this the best way?

How would my code change if I wanted to handle not only integers (my current plan) but strings, longs, doubles, bools, etc, etc. in the future? I guess make it accept a list of any type.

Fan

Answer 1

It's amazing what the Framework already does for us.

List<int> myValues;
string csv = String.Join(",", myValues.Select(x => x.ToString()).ToArray());

For the general case:

IEnumerable<T> myList;
string csv = String.Join(",", myList.Select(x => x.ToString()).ToArray());

As you can see, it's effectively no different. Beware that you might need to actually wrap x.ToString() in quotes (i.e., "\"" + x.ToString() + "\"") in case x.ToString() contains commas.

For an interesting read on a slight variant of this: see Comma Quibbling on Eric Lippert's blog.

Note: This was written before .NET 4.0 was officially released. Now we can just say

IEnumerable<T> sequence;
string csv = String.Join(",", sequence);

using the overload String.Join<T>(string, IEnumerable<T>). This method will automatically project each element x to x.ToString().

Lammas

List<int> does not have method Select in framework 3.5 unless I am missing something. – Seagirt

@ajeh: You're probably missing a using statement. – Lammas

Which specific import? – Seagirt

Try System.Linq.Enumerable (and of course you'll need System.Core.dll assembly, but presumably you already have that). You see, List<int> never has Select as a method. Rather, System.Linq.Enumerable defines Select as an extension method on IEnumerable<T>, of which List<int> is an example of. Thus, you need System.Linq.Enumerable in your imports to pick this extension method up. – Lammas

If you're dealing with numeric values and commas are a problem (depending on locale), one alternative is x.ToString(CultureInfo.InvariantCulture). This will use period as decimal separator. – Rozella

What if a cell value has quotes in it? – Ferroelectric

none of your answer is working for me. I am getting only a single line of comma seperated string which contains only the type name of the generic list<T> – Quentin

Answer 2

I explain it in-depth in this post. I'll just paste the code here with brief descriptions.

Here's the method that creates the header row. It uses the property names as column names.

private static void CreateHeader<T>(List<T> list, StreamWriter sw)
    {
        PropertyInfo[] properties = typeof(T).GetProperties();
        for (int i = 0; i < properties.Length - 1; i++)
        {
            sw.Write(properties[i].Name + ",");
        }
        var lastProp = properties[properties.Length - 1].Name;
        sw.Write(lastProp + sw.NewLine);
    }

This method creates all the value rows

private static void CreateRows<T>(List<T> list, StreamWriter sw)
    {
        foreach (var item in list)
        {
            PropertyInfo[] properties = typeof(T).GetProperties();
            for (int i = 0; i < properties.Length - 1; i++)
            {
                var prop = properties[i];
                sw.Write(prop.GetValue(item) + ",");
            }
            var lastProp = properties[properties.Length - 1];
            sw.Write(lastProp.GetValue(item) + sw.NewLine);
        }
    }

And here's the method that brings them together and creates the actual file.

public static void CreateCSV<T>(List<T> list, string filePath)
    {
        using (StreamWriter sw = new StreamWriter(filePath))
        {
            CreateHeader(list, sw);
            CreateRows(list, sw);
        }
    }

Marnamarne

This works very well. I improved this to pass the delimiter as a parameter, so any type of delimited file can be generated. CSVs are pain to deal with if the text contains commas, so I generate | delimited files using the improved version. Thanks! – Flash

But if you still prefer comma separated, but the data may contain commas then putting in sw.Write( "\"" + prop.GetValue(item) + "\","); and similar for the header will resolve that issue. – Marrano

if you are going to use quoted values (which is a good idea, btw), use this instead, to escape quotes in the content: sw.Write( "\"" + prop.GetValue(item).Replace("\"", "\"\"") + "\","); – Guideboard

Answer 3

in 3.5, i was still able to do this. Its much more simpler and doesnt need lambda.

String.Join(",", myList.ToArray<string>());

Din

ToArray() method of List<int> cannot be used with type argument in framework 3.5 unless I am missing something. – Seagirt

Brilliant. There is no need for ToArray<string> as child ToString() is used. – Proof

Answer 4

You can create an extension method that you can call on any IEnumerable:

public static string JoinStrings<T>(
    this IEnumerable<T> values, string separator)
{
    var stringValues = values.Select(item =>
        (item == null ? string.Empty : item.ToString()));
    return string.Join(separator, stringValues.ToArray());
}

Then you can just call the method on the original list:

string commaSeparated = myList.JoinStrings(", ");

Whitehorse

Answer 5

If any body wants to convert list of custom class objects instead of list of string then override the ToString method of your class with csv row representation of your class.

Public Class MyClass{
   public int Id{get;set;}
   public String PropertyA{get;set;}
   public override string ToString()
   {
     return this.Id+ "," + this.PropertyA;
   }
}

Then following code can be used to convert this class list to CSV with header column

string csvHeaderRow = String.Join(",", typeof(MyClass).GetProperties(BindingFlags.Public | BindingFlags.Instance).Select(x => x.Name).ToArray<string>()) + Environment.NewLine;
string csv= csvHeaderRow + String.Join(Environment.NewLine, MyClass.Select(x => x.ToString()).ToArray());

Cleanly

myExampleCollection.Select instead MyClass.Select – Plio

Answer 6

You can use String.Join.

String.Join(
  ",",
  Array.ConvertAll(
     list.ToArray(),
     element => element.ToString()
  )
);

Kreiker

No need to specify generic type parameters in call to ConvertAll here - both int and string will be inferred. – Maiga

Instead of doing Array.ConvertAll(...' you can just do list.ConvertAll(e=>e.ToString()).ToArray)`, just less typing. – Justificatory

string.Join(",", list); will do just fine :) – Proof

Answer 7

As the code in the link given by @Frank Create a CSV File from a .NET Generic List there was a little issue of ending every line with a , I modified the code to get rid of it.Hope it helps someone.

/// <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 csvCompletePath)
{
    if (list == null || list.Count == 0) return;

    //get type from 0th member
    Type t = list[0].GetType();
    string newLine = Environment.NewLine;

    if (!Directory.Exists(Path.GetDirectoryName(csvCompletePath))) Directory.CreateDirectory(Path.GetDirectoryName(csvCompletePath));

    if (!File.Exists(csvCompletePath)) File.Create(csvCompletePath);

    using (var sw = new StreamWriter(csvCompletePath))
    {
        //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
        sw.Write(string.Join(",", props.Select(d => d.Name).ToArray()) + newLine);

        //this acts as datarow
        foreach (T item in list)
        {
            //this acts as datacolumn
            var row = string.Join(",", props.Select(d => item.GetType()
                                                            .GetProperty(d.Name)
                                                            .GetValue(item, null)
                                                            .ToString())
                                                    .ToArray());
            sw.Write(row + newLine);

        }
    }
}

Beyrouth

Additional information: The process cannot access the file 'c:\temp\matchingMainWav.csv' because it is being used by another process. the folder dev exist , but not the file ... am i not using that right? – Vanhook

The File.Create method creates the file and opens a FileStream on the file. So your file is already open. You don't really need the file.Create method at all: – Justificatory

If any properties are null, is there a way around that? – Intercollegiate

@DanielJackson You can write a where clause in this statement sw.Write(string.Join(",", props.Select(d => d.Name).ToArray()) + newLine); Not tested but don't know what you are trying to achieve – Beyrouth

Answer 8

I like a nice simple extension method

 public static string ToCsv(this List<string> itemList)
         {
             return string.Join(",", itemList);
         }

Then you can just call the method on the original list:

string CsvString = myList.ToCsv();

Cleaner and easier to read than some of the other suggestions.

Luxuriate

Answer 9

CsvHelper library is very popular in the Nuget.You worth it,man! https://github.com/JoshClose/CsvHelper/wiki/Basics

Using CsvHelper is really easy. It's default settings are setup for the most common scenarios.

Here is a little setup data.

Actors.csv:

Id,FirstName,LastName  
1,Arnold,Schwarzenegger  
2,Matt,Damon  
3,Christian,Bale

Actor.cs (custom class object that represents an actor):

public class Actor
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Reading the CSV file using CsvReader:

var csv = new CsvReader( new StreamReader( "Actors.csv" ) );

var actorsList = csv.GetRecords();

Writing to a CSV file.

using (var csv = new CsvWriter( new StreamWriter( "Actors.csv" ) )) 
{
    csv.WriteRecords( actorsList );
}

Maidenhood

This is a good answer that may need updating. new CsvWriter( new StreamWriter( "Actors.csv" ) ) now requires CultureInfo as a second parameter. – Edla

Answer 10

For whatever reason, @AliUmair reverted the edit to his answer that fixes his code that doesn't run as is, so here is the working version that doesn't have the file access error and properly handles null object property values:

/// <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 csvCompletePath)
{
    if (list == null || list.Count == 0) return;

    //get type from 0th member
    Type t = list[0].GetType();
    string newLine = Environment.NewLine;

    if (!Directory.Exists(Path.GetDirectoryName(csvCompletePath))) Directory.CreateDirectory(Path.GetDirectoryName(csvCompletePath));

    using (var sw = new StreamWriter(csvCompletePath))
    {
        //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
        sw.Write(string.Join(",", props.Select(d => d.Name).ToArray()) + newLine);

        //this acts as datarow
        foreach (T item in list)
        {
            //this acts as datacolumn
            var row = string.Join(",", props.Select(d => $"\"{item.GetType().GetProperty(d.Name).GetValue(item, null)?.ToString()}\"")
                                                    .ToArray());
            sw.Write(row + newLine);

        }
    }
}

Smasher

Answer 11

Any solution work only if List a list(of string)

If you have a generic list of your own Objects like list(of car) where car has n properties, you must loop the PropertiesInfo of each car object.

Look at: http://www.csharptocsharp.com/generate-csv-from-generic-list

Fetial

can't you override ToString of the class and use the methods above? – Lippizaner

Answer 12

The problem with String.Join is that you are not handling the case of a comma already existing in the value. When a comma exists then you surround the value in Quotes and replace all existing Quotes with double Quotes.

String.Join(",",{"this value has a , in it","This one doesn't", "This one , does"});

See CSV Module

Declarative

Answer 13

Here is my extension method, it returns a string for simplicity but my implementation writes the file to a data lake.

It provides for any delimiter, adds quotes to string (in case they contain the delimiter) and deals will nulls and blanks.

    /// <summary>
    /// A class to hold extension methods for C# Lists 
    /// </summary>
    public static class ListExtensions
    {
        /// <summary>
        /// Convert a list of Type T to a CSV
        /// </summary>
        /// <typeparam name="T">The type of the object held in the list</typeparam>
        /// <param name="items">The list of items to process</param>
        /// <param name="delimiter">Specify the delimiter, default is ,</param>
        /// <returns></returns>
        public static string ToCsv<T>(this List<T> items, string delimiter = ",")
        {
            Type itemType = typeof(T);
            var props = itemType.GetProperties(BindingFlags.Public | BindingFlags.Instance).OrderBy(p => p.Name);

            var csv = new StringBuilder();

            // Write Headers
            csv.AppendLine(string.Join(delimiter, props.Select(p => p.Name)));

            // Write Rows
            foreach (var item in items)
            {
                // Write Fields
                csv.AppendLine(string.Join(delimiter, props.Select(p => GetCsvFieldasedOnValue(p, item))));
            }

            return csv.ToString();
        }

        /// <summary>
        /// Provide generic and specific handling of fields
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        private static object GetCsvFieldasedOnValue<T>(PropertyInfo p, T item)
        {
            string value = "";

            try
            {
                value = p.GetValue(item, null)?.ToString();
                if (value == null) return "NULL";  // Deal with nulls
                if (value.Trim().Length == 0) return ""; // Deal with spaces and blanks

                // Guard strings with "s, they may contain the delimiter!
                if (p.PropertyType == typeof(string))
                {
                    value = string.Format("\"{0}\"", value);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return value;
        }
    }

Usage:

 // Tab Delimited (TSV)
 var csv = MyList.ToCsv<MyClass>("\t");

Accountable

Answer 14

http://cc.davelozinski.com/c-sharp/the-fastest-way-to-read-and-process-text-files

This website did some extensive testing about how to write to a file using buffered writer, reading line by line seems to be the best way, using string builder was one of the slowest.

I use his techniques a great deal for writing stuff to file it works well.

Haunting

Answer 15

A general purpose ToCsv() extension method:

• Supports Int16/32/64, float, double, decimal, and anything supporting ToString()
• Optional custom join separator
• Optional custom selector
• Optional null/empty handling specification (*Opt() overloads)

Usage Examples:

"123".ToCsv() // "1,2,3"
"123".ToCsv(", ") // "1, 2, 3"
new List<int> { 1, 2, 3 }.ToCsv() // "1,2,3"

new List<Tuple<int, string>> 
{ 
    Tuple.Create(1, "One"), 
    Tuple.Create(2, "Two") 
}
.ToCsv(t => t.Item2);  // "One,Two"

((string)null).ToCsv() // throws exception
((string)null).ToCsvOpt() // ""
((string)null).ToCsvOpt(ReturnNullCsv.WhenNull) // null

Implementation

/// <summary>
/// Specifies when ToCsv() should return null.  Refer to ToCsv() for IEnumerable[T]
/// </summary>
public enum ReturnNullCsv
{
    /// <summary>
    /// Return String.Empty when the input list is null or empty.
    /// </summary>
    Never,

    /// <summary>
    /// Return null only if input list is null.  Return String.Empty if list is empty.
    /// </summary>
    WhenNull,

    /// <summary>
    /// Return null when the input list is null or empty
    /// </summary>
    WhenNullOrEmpty,

    /// <summary>
    /// Throw if the argument is null
    /// </summary>
    ThrowIfNull
}   

/// <summary>
/// Converts IEnumerable list of values to a comma separated string values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="values">The values.</param>        
/// <param name="joinSeparator"></param>
/// <returns>System.String.</returns>
public static string ToCsv<T>(
    this IEnumerable<T> values,            
    string joinSeparator = ",")
{
    return ToCsvOpt<T>(values, null /*selector*/, ReturnNullCsv.ThrowIfNull, joinSeparator);
}

/// <summary>
/// Converts IEnumerable list of values to a comma separated string values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="values">The values.</param>
/// <param name="selector">An optional selector</param>
/// <param name="joinSeparator"></param>
/// <returns>System.String.</returns>
public static string ToCsv<T>(
    this IEnumerable<T> values,
    Func<T, string> selector,            
    string joinSeparator = ",") 
{
    return ToCsvOpt<T>(values, selector, ReturnNullCsv.ThrowIfNull, joinSeparator);
}

/// <summary>
/// Converts IEnumerable list of values to a comma separated string values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="values">The values.</param>
/// <param name="returnNullCsv">Return mode (refer to enum ReturnNullCsv).</param>
/// <param name="joinSeparator"></param>
/// <returns>System.String.</returns>
public static string ToCsvOpt<T>(
    this IEnumerable<T> values,
    ReturnNullCsv returnNullCsv = ReturnNullCsv.Never,
    string joinSeparator = ",")
{
    return ToCsvOpt<T>(values, null /*selector*/, returnNullCsv, joinSeparator);
}

/// <summary>
/// Converts IEnumerable list of values to a comma separated string values.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="values">The values.</param>
/// <param name="selector">An optional selector</param>
/// <param name="returnNullCsv">Return mode (refer to enum ReturnNullCsv).</param>
/// <param name="joinSeparator"></param>
/// <returns>System.String.</returns>
public static string ToCsvOpt<T>(
    this IEnumerable<T> values, 
    Func<T, string> selector,
    ReturnNullCsv returnNullCsv = ReturnNullCsv.Never,
    string joinSeparator = ",")
{
    switch (returnNullCsv)
    {
        case ReturnNullCsv.Never:
            if (!values.AnyOpt())
                return string.Empty;
            break;

        case ReturnNullCsv.WhenNull:
            if (values == null)
                return null;
            break;

        case ReturnNullCsv.WhenNullOrEmpty:
            if (!values.AnyOpt())
                return null;
            break;

        case ReturnNullCsv.ThrowIfNull:
            if (values == null)
                throw new ArgumentOutOfRangeException("ToCsvOpt was passed a null value with ReturnNullCsv = ThrowIfNull.");
            break;

        default:
            throw new ArgumentOutOfRangeException("returnNullCsv", returnNullCsv, "Out of range.");
    }

    if (selector == null)
    {
        if (typeof(T) == typeof(Int16) || 
            typeof(T) == typeof(Int32) || 
            typeof(T) == typeof(Int64))
        {                   
            selector = (v) => Convert.ToInt64(v).ToStringInvariant();
        }
        else if (typeof(T) == typeof(decimal))
        {
            selector = (v) => Convert.ToDecimal(v).ToStringInvariant();
        }
        else if (typeof(T) == typeof(float) ||
                typeof(T) == typeof(double))
        {
            selector = (v) => Convert.ToDouble(v).ToString(CultureInfo.InvariantCulture);
        }
        else
        {
            selector = (v) => v.ToString();
        }            
    }

    return String.Join(joinSeparator, values.Select(v => selector(v)));
}

public static string ToStringInvariantOpt(this Decimal? d)
{
    return d.HasValue ? d.Value.ToStringInvariant() : null;
}

public static string ToStringInvariant(this Decimal d)
{
    return d.ToString(CultureInfo.InvariantCulture);
}

public static string ToStringInvariantOpt(this Int64? l)
{
    return l.HasValue ? l.Value.ToStringInvariant() : null;
}

public static string ToStringInvariant(this Int64 l)
{
    return l.ToString(CultureInfo.InvariantCulture);
}

public static string ToStringInvariantOpt(this Int32? i)
{
    return i.HasValue ? i.Value.ToStringInvariant() : null;
}

public static string ToStringInvariant(this Int32 i)
{
    return i.ToString(CultureInfo.InvariantCulture);
}

public static string ToStringInvariantOpt(this Int16? i)
{
    return i.HasValue ? i.Value.ToStringInvariant() : null;
}

public static string ToStringInvariant(this Int16 i)
{
    return i.ToString(CultureInfo.InvariantCulture);
}

Rainfall

Answer 16

The other answers work, but my issue is loading unknown data from the database, so I needed something a bit more robust than what's already here.

I wanted something that fit the following requirements:

• able to be opened in excel
• had to be able to handle date time formats in an excel compatible way
• had to automatically exclude linked entities (EF navigation properties)
• had to support column contents containing " and the delimiter ,
• had to support nullable columns
• had to support a wide array of data types
  • numbers of every kind
  • guids
  • datetimes
  • custom type definitions (ie name from a linked entity)

I used month/day/year formats for the date exports for compatibility reasons

public static IReadOnlyDictionary<System.Type, Func<object, string>> CsvTypeFormats = new Dictionary<System.Type, Func<object, string>> {
    // handles escaping column delimiter (',') and quote marks
    { typeof(string), x => string.IsNullOrWhiteSpace(x as string) ? null as string : $"\"{(x as string).Replace("\"", "\"\"")}\""},
    { typeof(DateTime), x => $"{x:M/d/yyyy H:m:s.fff}" },
    { typeof(DateTime?), x => x == null ? "" : $"{x:M/d/yyyy H:m:s.fff}" },
    { typeof(DateTimeOffset), x => $"{x:M/d/yyyy H:m:s.fff}" },
    { typeof(DateTimeOffset?), x => x == null ? "" : $"{x:M/d/yyyy H:m:s.fff}" },
};
public void WriteCsvContent<T>(ICollection<T> data, StringBuilder writer, IDictionary<System.Type, Func<object, string>> explicitMapping = null)
{
    var typeMappings = CsvTypeFormats.ToDictionary(x=>x.Key, x=>x.Value);
    if (explicitMapping != null) {
        foreach(var mapping in explicitMapping) {
            typeMappings[mapping.Key] = mapping.Value;
        }
    }
    var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
        .Where(x => IsSimpleType(x.PropertyType))
        .ToList();
    // header row
    writer.AppendJoin(',', props.Select(x => x.Name));
    writer.AppendLine();
    foreach (var item in data)
    {
        writer.AppendJoin(',',
            props.Select(prop => typeMappings.ContainsKey(prop.PropertyType)
                ? typeMappings[prop.PropertyType](prop.GetValue(item))
                : prop.GetValue(item)?.ToString() ?? ""
            )
            // escaping and special characters
            .Select(x => x != null && x != "" ? $"\"{x.Replace("\"", "\"\"")}\"" : null)
        );
        writer.AppendLine();
    }
}
private bool IsSimpleType(System.Type t)
{
    return
      t.IsPrimitive ||
      t.IsValueType ||
      t.IsEnum ||
      (t == typeof(string)) ||
      CsvTypeFormats.ContainsKey(t);
}

If your class uses fields instead of properties, change the GetProperties to GetFields and the PropertyType accessors to FieldType

Guideboard