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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

        }
    }
}

Answer 5

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

        }
    }
}

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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.

Answer 12

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

Answer 13

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

Answer 14

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

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

Answer 15

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(", ");

Answer 16

You can use String.Join.

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