Convert DataTable to CSV stream

Question

Currently have a DataTable, but wish to stream it to the user via a WebHandler. FileHelpers has CommonEngine.DataTableToCsv(dt, "file.csv"). However it saves it to a file. How can I save it to a stream instead? I know how to do it when I know the columns in advanced or they don't change, but I want to generate the column headings straight from the data table.

If I know the columns I just create the class:

[DelimitedRecord(",")]
public class MailMergeFields
{
    [FieldQuoted()]
    public string FirstName;
    [FieldQuoted()]
    public string LastName;
}

Then use FileHelperEngine and add the records:

FileHelperEngine engine = new FileHelperEngine(typeof(MailMergeFields));

MailMergeFields[] merge = new MailMergeFields[dt.Rows.Count + 1];

// add headers
merge[0] = new MailMergeFields();
merge[0].FirstName = "FirstName";
merge[0].LastName = "LastName";

int i = 1;              
// add records
foreach (DataRow dr in dt.Rows)
{
    merge[i] = new MailMergeFields();
    merge[i].FirstName = dr["Forename"];
    merge[i].LastName = dr["Surname"];
    i++;
}

Finally write to a stream:

TextWriter writer = new StringWriter();
engine.WriteStream(writer, merge);
context.Response.Write(writer.ToString());

Unfortunately as I don't know the columns before hand, I can't create the class before hand.

Monosyllable

The File Helpers library is open source. Why don't you crack into it and add your own method? – Deon

You may check this gist.github.com/riyadparvez/4467668 – Anion

@user: This gist contains a bug where entries with commas will not be handled correctly. See https://mcmap.net/q/65565/-dealing-with-commas-in-a-csv-file/1461424 – Janniejanos

Answer 1

You can just write something quickly yourself:

public static class Extensions
{
    public static string ToCSV(this DataTable table)
    {
        var result = new StringBuilder();
        for (int i = 0; i < table.Columns.Count; i++)
        {
            result.Append(table.Columns[i].ColumnName);
            result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
        }

        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(row[i].ToString());
                result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
            }
        }

        return result.ToString();
    }
}

And to test:

  public static void Main()
  {
        DataTable table = new DataTable();
        table.Columns.Add("Name");
        table.Columns.Add("Age");
        table.Rows.Add("John Doe", "45");
        table.Rows.Add("Jane Doe", "35");
        table.Rows.Add("Jack Doe", "27");
        var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(table.ToCSV());
        MemoryStream stream = new MemoryStream(bytes);

        StreamReader reader = new StreamReader(stream);
        Console.WriteLine(reader.ReadToEnd());
  }

EDIT: Re your comments:

It depends on how you want your csv formatted but generally if the text contains special characters, you want to enclose it in double quotes ie: "my,text". You can add checking in the code that creates the csv to check for special characters and encloses the text in double quotes if it is. As for the .NET 2.0 thing, just create it as a helper method in your class or remove the word this in the method declaration and call it like so : Extensions.ToCsv(table);

Electuary

What about when the data includes a quote, newline or comma? – Monosyllable

This works for me. Tweaked a bit for my needs. Excel displays newlines as ? though (have same problem when exporting from SQL Server) – Monosyllable

Would suggest adding Following lines to ToCSV function: string s = result.ToString(); s = s.TrimEnd(new char[] { '\r', '\n' }); return s; – Legume

Answer 2

Update 1

I have modified it to use StreamWriter instead, add an option to check if you need column headers in your output.

public static bool DataTableToCSV(DataTable dtSource, StreamWriter writer, bool includeHeader)
{
    if (dtSource == null || writer == null) return false;

    if (includeHeader)
    {
        string[] columnNames = dtSource.Columns.Cast<DataColumn>().Select(column => "\"" + column.ColumnName.Replace("\"", "\"\"") + "\"").ToArray<string>();
        writer.WriteLine(String.Join(",", columnNames));
        writer.Flush();
    }

    foreach (DataRow row in dtSource.Rows)
    {
        string[] fields = row.ItemArray.Select(field => "\"" + field.ToString().Replace("\"", "\"\"") + "\"").ToArray<string>();
        writer.WriteLine(String.Join(",", fields));
        writer.Flush();
    }

    return true;
}

As you can see, you can choose the output by initial StreamWriter, if you use StreamWriter(Stream BaseStream), you can write csv into MemeryStream, FileStream, etc.

Origin

I have an easy datatable to csv function, it serves me well:

    public static void DataTableToCsv(DataTable dt, string csvFile)
    {
        StringBuilder sb = new StringBuilder();

        var columnNames = dt.Columns.Cast<DataColumn>().Select(column => "\"" + column.ColumnName.Replace("\"", "\"\"") + "\"").ToArray();
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in dt.Rows)
        {
            var fields = row.ItemArray.Select(field => "\"" + field.ToString().Replace("\"", "\"\"") + "\"").ToArray();
            sb.AppendLine(string.Join(",", fields));
        }

        File.WriteAllText(csvFile, sb.ToString(), Encoding.Default);
    }

Edra

Answer 3

If you can turn your datatable into an IEnumerable this should work for you...

    Response.Clear();
    Response.Buffer = true;

    Response.AddHeader("content-disposition", "attachment;filename=FileName.csv");
    Response.Charset = "";
    Response.ContentType = "application/text";
    Response.Output.Write(ExampleClass.ConvertToCSV(GetListOfObject(), typeof(object)));
    Response.Flush();
    Response.End();



public static string ConvertToCSV(IEnumerable col, Type type)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder header = new StringBuilder();

            // Gets all  properies of the class
            PropertyInfo[] pi = type.GetProperties();

            // Create CSV header using the classes properties
            foreach (PropertyInfo p in pi)
            {
                header.Append(p.Name + ",");
            }

            sb.AppendLine(header.ToString().Remove(header.Length));

            foreach (object t in col)
            {
                StringBuilder body = new StringBuilder();

                // Create new item
                foreach (PropertyInfo p in pi)
                {
                    object o = p.GetValue(t, null);
                    body.Append(o.ToString() + ",");
                }

                sb.AppendLine(body.ToString().Remove(body.Length));
            }
            return sb.ToString();
        }

Loricate

Thanks, fyi - header.Length & body.Length should be -1 – Renner

Answer 4

public void CreateCSVFile(DataTable dt, string strFilePath,string separator)
        {               
            #region Export Grid to CSV
            // Create the CSV file to which grid data will be exported.

            StreamWriter sw = new StreamWriter(strFilePath, false); 
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {    
                sw.Write(dt.Columns[i]);    
                if (i < iColCount - 1)
                {
                    sw.Write(separator);
                }  
            }    

            sw.Write(sw.NewLine);

            // Now write all the rows.
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        sw.Write(dr[i].ToString());  
                    }

                    if (i < iColCount - 1)
                    {
                        sw.Write(separator);
                    }
                }
                sw.Write(sw.NewLine);
            }

            sw.Close();
            #endregion
        }

Observant

Answer 5

I don't know if this converted from VB to C# ok but if you don't want quotes around your numbers, you might compare the data type as follows..

public string DataTableToCSV(DataTable dt)
{
    StringBuilder sb = new StringBuilder();
    if (dt == null)
        return "";

    try {
        // Create the header row
        for (int i = 0; i <= dt.Columns.Count - 1; i++) {
            // Append column name in quotes
            sb.Append("\"" + dt.Columns[i].ColumnName + "\"");
            // Add carriage return and linefeed if last column, else add comma
            sb.Append(i == dt.Columns.Count - 1 ? "\n" : ",");
        }


        foreach (DataRow row in dt.Rows) {
            for (int i = 0; i <= dt.Columns.Count - 1; i++) {
                // Append value in quotes
                //sb.Append("""" & row.Item(i) & """")

                // OR only quote items that that are equivilant to strings
                sb.Append(object.ReferenceEquals(dt.Columns[i].DataType, typeof(string)) || object.ReferenceEquals(dt.Columns[i].DataType, typeof(char)) ? "\"" + row[i] + "\"" : row[i]);

                // Append CR+LF if last field, else add Comma
                sb.Append(i == dt.Columns.Count - 1 ? "\n" : ",");
            }
        }
        return sb.ToString;
    } catch (Exception ex) {
        // Handle the exception however you want
        return "";
    }

}

Nonnah

Answer 6

If you wish to stream the CSV out to the user without creating a file then I found the following to be the simplest method. You can use any extension/method to create the ToCsv() function (which returns a string based on the given DataTable).

        var report = myDataTable.ToCsv();
        var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(report);

        Response.Buffer = true;
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=report.csv");
        Response.ContentType = "text/csv";
        Response.BinaryWrite(bytes);
        Response.End();

Scrabble

Answer 7

You can try using something like this. In this case I used one stored procedure to get more data tables and export all of them using CSV.

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace bo
{
class Program
{
    static private void CreateCSVFile(DataTable dt, string strFilePath)
    {
        #region Export Grid to CSV
        // Create the CSV file to which grid data will be exported.
        StreamWriter sw = new StreamWriter(strFilePath, false);
        int iColCount = dt.Columns.Count;

        // First we will write the headers.

        //DataTable dt = m_dsProducts.Tables[0];
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dt.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(";");
            }
        }
        sw.Write(sw.NewLine);

        // Now write all the rows.
        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount -1 )
                {
                    sw.Write(";");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();

        #endregion
    }
    static void Main(string[] args)
    {
        string strConn = "connection string to sql";
        string direktorij = @"d:";
        SqlConnection conn = new SqlConnection(strConn); 
        SqlCommand command = new SqlCommand("sp_ado_pos_data", conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add('@skl_id', SqlDbType.Int).Value = 158;
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            string datoteka  = (string.Format(@"{0}tablea{1}.csv", direktorij, i));
            DataTable tabela = ds.Tables[i];
            CreateCSVFile(tabela,datoteka );
            Console.WriteLine("Generišem tabelu {0}", datoteka);
        }
        Console.ReadKey();
    }
  }
}

Allegra

Answer 8

I've used the following code, pillaged from someone's blog (pls forgive lack of citation). It takes care of quotations, newline and comma in a reasonably elegant way by quoting out each field value.

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.      
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <returns>Resulting CSV-style string</returns>
    public static string ToCSV(this DataTable table)
    {
        return ToCSV(table, ",", true);
    }

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <param name="includeHeader">true - include headers<br/>
    /// false - do not include header column</param>
    /// <returns>Resulting CSV-style string</returns>
    public static string ToCSV(this DataTable table, bool includeHeader)
    {
        return ToCSV(table, ",", includeHeader);
    }

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <param name="includeHeader">true - include headers<br/>
    /// false - do not include header column</param>
    /// <returns>Resulting CSV-style string</returns>
     public static string ToCSV(this DataTable table, string delimiter, bool includeHeader)
    {
        var result = new StringBuilder();

        if (includeHeader)
        {
            foreach (DataColumn column in table.Columns)
            {
                result.Append(column.ColumnName);
                result.Append(delimiter);
            }

            result.Remove(--result.Length, 0);
            result.Append(Environment.NewLine);
        }

        foreach (DataRow row in table.Rows)
        {
            foreach (object item in row.ItemArray)
            {
                if (item is DBNull)
                    result.Append(delimiter);
                else
                {
                    string itemAsString = item.ToString();
                    // Double up all embedded double quotes
                    itemAsString = itemAsString.Replace("\"", "\"\"");

                    // To keep things simple, always delimit with double-quotes
                    // so we don't have to determine in which cases they're necessary
                    // and which cases they're not.
                    itemAsString = "\"" + itemAsString + "\"";

                    result.Append(itemAsString + delimiter);
                }
            }

            result.Remove(--result.Length, 0);
            result.Append(Environment.NewLine);
        }

        return result.ToString();
    }

Abjuration

Answer 9

BFree's answer worked for me. I needed to post the stream right to the browser. Which I'd imagine is a common alternative. I added the following to BFree's Main() code to do this:

//StreamReader reader = new StreamReader(stream);
//Console.WriteLine(reader.ReadToEnd());

string fileName = "fileName.csv";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", fileName));
stream.Position = 0;
stream.WriteTo(HttpContext.Current.Response.OutputStream);

Kelikeligot