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

User · Answer

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

User · Answer

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 lt DataColumn gt    Select column   gt         column ColumnName Replace                       ToArray lt string gt             writer WriteLine String Join      columnNames            writer Flush               foreach  DataRow row in dtSource Rows                string   fields   row ItemArray Select field   gt         field ToString   Replace                       ToArray lt string gt             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 lt DataColumn gt    Select column   gt         column ColumnName Replace                       ToArray            sb AppendLine string Join      columnNames             foreach  DataRow row in dt Rows                        var fields   row ItemArray Select field   gt         field ToString   Replace                       ToArray                sb AppendLine string Join      fields                       File WriteAllText csvFile  sb ToString    Encoding Default

User · Answer

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            lt summary gt          Converts the passed in data table to a CSV-style string                 lt  summary gt           lt param name  table  gt Table to convert lt  param gt           lt returns gt Resulting CSV-style string lt  returns gt      public static string ToCSV this DataTable table                return ToCSV table       true                   lt summary gt          Converts the passed in data table to a CSV-style string           lt  summary gt           lt param name  table  gt Table to convert lt  param gt           lt param name  includeHeader  gt true - include headers lt br  gt          false - do not include header column lt  param gt           lt returns gt Resulting CSV-style string lt  returns gt      public static string ToCSV this DataTable table  bool includeHeader                return ToCSV table       includeHeader                   lt summary gt          Converts the passed in data table to a CSV-style string           lt  summary gt           lt param name  table  gt Table to convert lt  param gt           lt param name  includeHeader  gt true - include headers lt br  gt          false - do not include header column lt  param gt           lt returns gt Resulting CSV-style string lt  returns gt       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

User · Answer

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

User · Answer

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  lt  iColCount  i                          sw Write dt Columns i                if  i  lt  iColCount - 1                                sw Write                                       sw Write sw NewLine               Now write all the rows          foreach  DataRow dr in dt Rows                        for  int i   0  i  lt  iColCount  i                                  if   Convert IsDBNull dr i                                          sw Write dr i  ToString                                       if  i  lt  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  lt  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

User · Answer

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  lt  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  lt  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

User · Answer

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  lt   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  lt   dt Columns Count - 1  i                         Append value in quotes                   sb Append       amp  row Item i   amp                            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

User · Answer

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  lt  iColCount  i                                      sw Write dt Columns i                        if  i  lt  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  lt  iColCount  i                                          if   Convert IsDBNull dr i                                                  sw Write dr i  ToString                                                  if  i  lt  iColCount - 1                                                sw Write separator                                                           sw Write sw NewLine                              sw Close                 endregion

User · Answer

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

[c#] Convert DataTable to CSV stream

Examples related to c#

Examples related to csv

Examples related to datatable