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.

Answer 1

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

Answer 2

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

Answer 3

Not sure why you can't 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);

Answer 4

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