How can I turn a DataTable to a CSV?

Question

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<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => 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<string> columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field => 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<string> fields = row.ItemArray.Select(field => 
      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.

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

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

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.