How can I turn a DataTable to a CSV

Question

Could somebody please tell me why the following code is not working   The data is saved into the csv file  however the data is not separated   It all exists within the first cell of each row    StringBuilder sb   new StringBuilder     foreach  DataColumn col in dt Columns        sb Append col ColumnName            sb Remove sb Length - 1  1   sb Append Environment NewLine    foreach  DataRow row in dt Rows        for  int i   0  i  lt  dt Columns Count  i                  sb Append row i  ToString                      sb Append Environment NewLine      File WriteAllText  test csv   sb ToString       Thanks

User · Answer

Read this and this   A better implementation would be  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                   File WriteAllText  test csv   result ToString

User · Answer

Possibly  most easy way will be to use   https   github com ukushu DataExporter  especially in case of your data of datatable containing  r n characters or separator symbol inside of your dataTable cells  Almost all of other answers will not work with such cells   only you need is to write the following code   Csv csv   new Csv   t     Needed delimiter   var columnNames   dt Columns Cast lt DataColumn gt         Select column   gt  column ColumnName  ToArray     csv AddRow columnNames    foreach  DataRow row in dt Rows        var fields   row ItemArray Select field   gt  field ToString    ToArray      csv AddRow fields         csv Save

User · Answer

Here is an enhancement to vc-74 s post that handles commas the same way Excel does  Excel puts quotes around data if the data has a comma but doesn t quote if the data doesn t have a comma       public static string ToCsv this DataTable inDataTable  bool inIncludeHeaders   true                var builder   new StringBuilder            var columnNames   inDataTable Columns Cast lt DataColumn gt    Select column   gt  column ColumnName           if  inIncludeHeaders              builder AppendLine string Join      columnNames            foreach  DataRow row in inDataTable Rows                        var fields   row ItemArray Select field   gt  field ToString   WrapInQuotesIfContains                    builder AppendLine string Join      fields                       return builder ToString               public static string WrapInQuotesIfContains this string inString  string inSearchString                if  inString Contains inSearchString               return        inString                return inString

User · Answer

I wrapped this up into an extension class  which allows you to call   myDataTable WriteToCsvFile  C   MyDataTable csv      on any DataTable   public static class DataTableExtensions        public static void WriteToCsvFile this DataTable dataTable  string filePath                 StringBuilder fileContent   new StringBuilder             foreach  var col in dataTable Columns                         fileContent Append col ToString                              fileContent Replace      System Environment NewLine  fileContent Length - 1  1            foreach  DataRow dr in dataTable Rows                         foreach  var column in dr ItemArray                                 fileContent Append        column ToString                                        fileContent Replace      System Environment NewLine  fileContent Length - 1  1                      System IO File WriteAllText filePath  fileContent ToString

User · Answer

I did this recently but included double quotes around my values   For example  change these two lines   sb Append        col ColumnName                sb Append        row i  ToString

User · Answer

A new extension function based on Paul Grimshaw s answer   I cleaned it up and added the ability to handle unexpected data   Empty Data  Embedded Quotes  and comma s in the headings      It also returns a string which is more flexible   It returns Null if the table object does not contain any structure       public static string ToCsv this DataTable dataTable            StringBuilder sbData   new StringBuilder                Only return Null if there is no structure          if  dataTable Columns Count    0              return null           foreach  var col in dataTable Columns                if  col    null                  sbData Append                   else                 sbData Append        col ToString   Replace                                            sbData Replace      System Environment NewLine  sbData Length - 1  1            foreach  DataRow dr in dataTable Rows                foreach  var column in dr ItemArray                    if  column    null                      sbData Append                       else                     sbData Append        column ToString   Replace                                                   sbData Replace      System Environment NewLine  sbData Length - 1  1                      return sbData ToString            You call it as follows   var csvData   dataTableOject ToCsv

User · Answer

In case anyone else stumbles on this  I was using File ReadAllText to get CSV data and then I modified it and wrote it back with File WriteAllText  The  r n CRLFs were fine but the  t tabs were ignored when Excel opened it   All solutions in this thread so far use a comma delimiter but that doesn t matter   Notepad showed the same format in the resulting file as in the source  A Diff even showed the files as identical  But I got a clue when I opened the file in Visual Studio with a binary editor  The source file was Unicode but the target was ASCII  To fix  I modified both ReadAllText and WriteAllText with third argument set as System Text Encoding Unicode  and from there Excel was able to open the updated file

User · Answer

StringBuilder sb   new StringBuilder            SaveFileDialog fileSave   new SaveFileDialog            IEnumerable lt string gt  columnNames   tbCifSil Columns Cast lt DataColumn gt                                               Select column   gt  column ColumnName           sb AppendLine string Join      columnNames             foreach  DataRow row in tbCifSil Rows                        IEnumerable lt string gt  fields   row ItemArray Select field   gt string Concat       field ToString   Replace                                    sb AppendLine string Join      fields                       fileSave ShowDialog            File WriteAllText fileSave FileName  sb ToString

User · Answer

4 lines of code   public static string ToCSV DataTable tbl        StringBuilder strb   new StringBuilder           column headers     strb AppendLine string Join      tbl Columns Cast lt DataColumn gt             Select s   gt         s ColumnName                   rows     tbl AsEnumerable   Select s   gt  strb AppendLine          string Join      s ItemArray Select              i   gt         i ToString              ToList         return strb ToString        Note that the ToList   at the end is important  I need something to force an expression evaluation   If I was code golfing  I could use Min   instead   Also note that the result will have a newline at the end because of the last call to AppendLine     You may not want this   You can simply call TrimEnd   to remove it

User · Answer

The error is the list separator   Instead of writing sb Append something           you should put something like sb Append something      System Globalization CultureInfo CurrentCulture TextInfo ListSeparator    You must put the list separator character configured in your operating system  like in the example above   or the list separator in the client machine where the file is going to be watched  Another option would be to configure it in the app config or web config as a parammeter of your application

User · Answer

Try to put   instead of    Hope it helps

User · Answer

Try changing sb Append Environment NewLine   to sb AppendLine        StringBuilder sb   new StringBuilder              foreach  DataColumn col in dt Columns                              sb Append col ColumnName                               sb Remove sb Length - 1  1            sb AppendLine               foreach  DataRow row in dt Rows                              for  int i   0  i  lt  dt Columns Count  i                                                sb Append row i  ToString                                                 sb AppendLine                          File WriteAllText  test csv   sb ToString

User · Answer

Here is my solution  based on previous answers by Paul Grimshaw and Anthony VO  I ve submitted the code in a C  project on Github  My main contribution is to eliminate explicitly creating and manipulating a StringBuilder and instead working only with IEnumerable  This avoids the allocation of a big buffer in memory  public static class Util       public static string EscapeQuotes this string self            return self  Replace  quot   quot  quot    quot   quot   quot  quot       quot  quot              public static string Surround this string self  string before  string after            return   quot  before  self  after  quot              public static string Quoted this string self  string quotes    quot   quot  quot             return self Surround quotes  quotes              public static string QuotedCSVFieldIfNecessary this string self            return  self    null     quot  quot    self Contains   quot      self Quoted     self              public static string ToCsvField this string self            return self EscapeQuotes   QuotedCSVFieldIfNecessary               public static string ToCsvRow this IEnumerable lt string gt  self           return string Join  quot   quot   self Select ToCsvField               public static IEnumerable lt string gt  ToCsvRows this DataTable self                      yield return self Columns OfType lt object gt    Select c   gt  c ToString    ToCsvRow            foreach  var dr in self Rows OfType lt DataRow gt                 yield return dr ItemArray Select item   gt  item ToString    ToCsvRow               public static void ToCsvFile this DataTable self  string path            File WriteAllLines path  self ToCsvRows              This approach combines nicely with converting IEnumerable to DataTable as asked here

User · Answer

public void ExpoetToCSV DataTable dtDataTable  string strFilePath         StreamWriter sw   new StreamWriter strFilePath  false         headers        for  int i   0  i  lt  dtDataTable Columns Count  i                  sw Write dtDataTable Columns i  ToString   Trim             if  i  lt  dtDataTable Columns Count - 1                        sw Write                           sw Write sw NewLine       foreach  DataRow dr in dtDataTable Rows                for  int i   0  i  lt  dtDataTable Columns Count  i                          if   Convert IsDBNull dr i                                  string value   dr i  ToString   Trim                    if  value Contains                                             value   String Format     0      value                       sw Write value                                     else                                       sw Write dr i  ToString   Trim                                                 if  i  lt  dtDataTable Columns Count - 1                                sw Write                                       sw Write sw NewLine             sw Close

User · Answer

To write to a file  I think the following method is the most efficient and straightforward   You can add quotes if you want   public static void WriteCsv DataTable dt  string path        using  var writer   new StreamWriter path             writer WriteLine string Join      dt Columns Cast lt DataColumn gt    Select dc   gt  dc ColumnName             foreach  DataRow row in dt Rows                writer WriteLine string Join      row ItemArray

User · Answer

To mimic Excel CSV   public static string Convert DataTable dt        StringBuilder sb   new StringBuilder         IEnumerable lt string gt  columnNames   dt Columns Cast lt DataColumn gt                                             Select column   gt  column ColumnName       sb AppendLine string Join      columnNames         foreach  DataRow row in dt Rows                IEnumerable lt string gt  fields   row ItemArray Select field   gt                        string s   field ToString   Replace                            if s Contains                       s   string Concat       s                     return s                      sb AppendLine string Join      fields               return sb ToString   Trim

User · Answer

If your calling code is referencing the System Windows Forms assembly  you may consider a radically different approach    My strategy is to use the functions already provided by the framework to accomplish this in very few lines of code and without having to loop through columns and rows  What the code below does is programmatically create a DataGridView on the fly and set the DataGridView DataSource to the DataTable  Next  I programmatically select all the cells  including the header  in the DataGridView and call DataGridView GetClipboardContent    placing the results into the Windows Clipboard  Then  I  paste  the contents of the clipboard into a call to File WriteAllText    making sure to specify the formatting of the  paste  as TextDataFormat CommaSeparatedValue   Here is the code   public static void DataTableToCSV DataTable Table  string Filename        using DataGridView dataGrid   new DataGridView                     Save the current state of the clipboard so we can restore it after we are done         IDataObject objectSave   Clipboard GetDataObject                Set the DataSource         dataGrid DataSource   Table             Choose whether to write header  Use EnableWithoutHeaderText instead to omit header          dataGrid ClipboardCopyMode   DataGridViewClipboardCopyMode EnableAlwaysIncludeHeaderText             Select all the cells         dataGrid SelectAll               Copy  set clipboard          Clipboard SetDataObject dataGrid GetClipboardContent                Paste  get the clipboard and serialize it to a file          File WriteAllText Filename Clipboard GetText TextDataFormat CommaSeparatedValue                              Restore the current state of the clipboard so the effect is seamless         if objectSave    null     If we try to set the Clipboard to an object that is null  it will throw                          Clipboard SetDataObject objectSave                       Notice I also make sure to preserve the contents of the clipboard before I begin  and restore it once I m done  so the user does not get a bunch of unexpected garbage next time the user tries to paste  The main caveats to this approach is 1  Your class has to reference System Windows Forms  which may not be the case in a data abstraction layer  2  Your assembly will have to be targeted for  NET 4 5 framework  as DataGridView does not exist in 4 0  and 3  The method will fail if the clipboard is being used by another process   Anyways  this approach may not be right for your situation  but it is interesting none the less  and can be another tool in your toolbox

User · Answer

The following shorter version opens fine in Excel  maybe your issue was the trailing comma   net   3 5  StringBuilder sb   new StringBuilder      string   columnNames   dt Columns Cast lt DataColumn gt                                       Select column   gt  column ColumnName                                     ToArray    sb AppendLine string Join      columnNames     foreach  DataRow row in dt Rows        string   fields   row ItemArray Select field   gt  field ToString                                         ToArray        sb AppendLine string Join      fields       File WriteAllText  test csv   sb ToString        net    4 0  And as Tim pointed out  if you are on  net  4  you can make it even shorter   StringBuilder sb   new StringBuilder      IEnumerable lt string gt  columnNames   dt Columns Cast lt DataColumn gt                                       Select column   gt  column ColumnName   sb AppendLine string Join      columnNames     foreach  DataRow row in dt Rows        IEnumerable lt string gt  fields   row ItemArray Select field   gt  field ToString         sb AppendLine string Join      fields       File WriteAllText  test csv   sb ToString       As suggested by Christian  if you want to handle special characters escaping in fields  replace the loop block by   foreach  DataRow row in dt Rows        IEnumerable lt string gt  fields   row ItemArray Select field   gt         string Concat       field ToString   Replace                            sb AppendLine string Join      fields        And last suggestion  you could write the csv content line by line instead of as a whole document  to avoid having a big document in memory

[c#] How can I turn a DataTable to a CSV?

Examples related to c#

Examples related to csv

Examples related to datatable