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 < dt.Columns.Count; i++)
    {
        sb.Append(row[i].ToString() + ",");
    }

    sb.Append(Environment.NewLine);
}

File.WriteAllText("test.csv", sb.ToString());

Thanks.

Dosser

You can check this gist.github.com/riyadparvez/4467668 – Discolor

I developed high performance Extention. check this answer – Torin

Answer 1

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.

Lidstone

No need to copy the ItemArray to a new String[], you can omit .ToArray() with .NET 4 and use the String.Join overload that takes an IEnumerable<T> (edited). – Adversative

@TimSchmelter, yes but these overloads were introduced in .net4, the code will not compile if the OP uses .net<4 – Lidstone

This method doesnt take into account a comma inside a column value. – Fluorspar

Instead IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString().Replace("\"","\"\"")); sb.AppendLine("\""+string.Join("\",\"", fields)+"\""); – Fluorspar

This was so close. Excel only puts quotes around fields with commas and there are not quotes around fields without commas. I have to duplicate what Excel does. I'll just use a loop instead of string.Join(). – Wanyen

If a field is empty, it's adding   inside it. – Hereunto

@Hereunto What do you mean? This answer uses only db components, and &nbsp is typical of HTML/XML documents. It's not the above code that produces it unless the table contains   explicitly – Lidstone

Caution: This is still consuming a lot of memory and can easily cause OutOfMemoryException. – Dicta

@Dicta This answer only shows ways to fix the OP's particular issue. You are right that this code could consume a lot of memory in case the datatable is very large, in which case writing to the file line by line would be a better approach. – Lidstone

Answer 2

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

Hoyt

Answer 3

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

Shores

This one is the best out of the rest here. Well done. Thanks – Construe

Fantastic solution. Added comments locally, but was able to use out of the box without having to climb the mountain. Thank you. – Photon

Loved this! I used it as a non-static method and just passed my DataTable as a parameter. Worked great, thank you. – Brumbaugh

Answer 4

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.

Heinrik

using the Clipboard is not needed #40726517. .GetClipboardContent also handles few edge cases of values that contain ,. ", \t (it converts tab to space) – Tryst

This is good, but what if someone is using the machine at the same time and puts something in to the Clipboard at the critical moment. – Scoliosis

Answer 5

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 < dt.Columns.Count; i++)             
    {                 
        sb.Append(row[i].ToString() + ",");             
    }              

    sb.AppendLine();         
}          

File.WriteAllText("test.csv", sb.ToString());

Celadon

That will then give two carraige returns. – Dosser

@alexl: That is what I was originally going with, but it was off the top of my head until VS fired up :o) – Celadon

Answer 6

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() + "\","); 

Utopianism

Thanks for the suggestion, but they all the data is still within the first cell of each row? – Dosser

Answer 7

4 lines of code:

public static string ToCSV(DataTable tbl)
{
    StringBuilder strb = new StringBuilder();

    //column headers
    strb.AppendLine(string.Join(",", tbl.Columns.Cast<DataColumn>()
        .Select(s => "\"" + s.ColumnName + "\"")));

    //rows
    tbl.AsEnumerable().Select(s => strb.AppendLine(
        string.Join(",", s.ItemArray.Select(
            i => "\"" + 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.

Lavolta

Answer 8

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<DataColumn>().Select(dc => dc.ColumnName)));
        foreach (DataRow row in dt.Rows) {
            writer.WriteLine(string.Join(",", row.ItemArray));
        }
    }
}

Poler

Answer 9

Try to put ; instead of ,

Hope it helps

Wesleywesleyan

Answer 10

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.

January

Answer 11

Read this and this?

---

A better implementation would be

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" : ",");
    }
}
 File.WriteAllText("test.csv", result.ToString());

Segovia

Answer 12

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<DataColumn>().Select(column => column.ColumnName);
        if (inIncludeHeaders)
            builder.AppendLine(string.Join(",", columnNames));
        foreach (DataRow row in inDataTable.Rows)
        {
            var fields = row.ItemArray.Select(field => 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;
    }

Wanyen

Answer 13

To mimic Excel CSV:

public static string Convert(DataTable dt)
{
    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 =>
        {
            string s = field.ToString().Replace("\"", "\"\"");
            if(s.Contains(','))
                s = string.Concat("\"", s, "\"");
            return s;
        });
        sb.AppendLine(string.Join(",", fields));
    }

    return sb.ToString().Trim();
}

Vichy

Answer 14

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("\"", "\"\"") ?? "";
    }

    public static string Surround(this string self, string before, string after) {
        return $"{before}{self}{after}";
    }

    public static string Quoted(this string self, string quotes = "\"") {
        return self.Surround(quotes, quotes);
    }

    public static string QuotedCSVFieldIfNecessary(this string self)
    {
        return (self == null) ? "" : (self.Contains('"') || self.Contains('\r') || self.Contains('\n') || self.Contains(',')) ? self.Quoted() : self;
    }

    public static string ToCsvField(this string self) {
        return self.EscapeQuotes().QuotedCSVFieldIfNecessary();
    }

    public static string ToCsvRow(this IEnumerable<string> self){
        return string.Join(",", self.Select(ToCsvField));
    }

    public static IEnumerable<string> ToCsvRows(this DataTable self) {          
        yield return self.Columns.OfType<object>().Select(c => c.ToString()).ToCsvRow();
        foreach (var dr in self.Rows.OfType<DataRow>())
            yield return dr.ItemArray.Select(item => 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.

Kavanagh

Answer 15

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<DataColumn>().
    Select(column => column.ColumnName).ToArray();

csv.AddRow(columnNames);

foreach (DataRow row in dt.Rows)
{
    var fields = row.ItemArray.Select(field => field.ToString()).ToArray;
    csv.AddRow(fields);   
}

csv.Save();

Tenacious

it says "Package 'dataexporter 1.1.0' has a package type 'DotnetTool' that is not supported by project 'LoyaltyCard'. " – Lanalanae

just copy file CSV.cs into your project and import default VS library with VisualBasic functions. – Tenacious

Caution: this is still consuming a lot of memory for larger datasets and can easily cause a OutOfMemoryException. – Dicta

you can try to use original VB library that is wrapped in DataExporter. But(!) original VB library is also does not work with really huge files - there are exist some limitations. – Tenacious

Answer 16

StringBuilder sb = new StringBuilder();
        SaveFileDialog fileSave = new SaveFileDialog();
        IEnumerable<string> columnNames = tbCifSil.Columns.Cast<DataColumn>().
                                          Select(column => column.ColumnName);
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in tbCifSil.Rows)
        {
            IEnumerable<string> fields = row.ItemArray.Select(field =>string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
            sb.AppendLine(string.Join(",", fields));
        }

        fileSave.ShowDialog();
        File.WriteAllText(fileSave.FileName, sb.ToString());

Page

Welcome to StackOverflow! Answers are best when they include a description of the code snippet. I've personally found that when variable names line up between the question and answer, they are more helpful to me. – Serafinaserafine

Answer 17

public void ExpoetToCSV(DataTable dtDataTable, string strFilePath)
{

    StreamWriter sw = new StreamWriter(strFilePath, false);
    //headers   
    for (int i = 0; i < dtDataTable.Columns.Count; i++)
    {
        sw.Write(dtDataTable.Columns[i].ToString().Trim());
        if (i < dtDataTable.Columns.Count - 1)
        {
            sw.Write(",");
        }
    }
    sw.Write(sw.NewLine);
    foreach (DataRow dr in dtDataTable.Rows)
    {
        for (int i = 0; i < 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 < dtDataTable.Columns.Count - 1)
            {
                sw.Write(",");
            }
        }
        sw.Write(sw.NewLine);
    }
    sw.Close();
}

Scottyscotus

Answer 18

Most existing answers can easily cause OutOfMemoryException, so I decided to write my own answer.

DON' T DO THIS:

using a DataSet + StringBuilder causes the data to occupy the memory 3x at once:

1. Load All Data into DataSet
2. Copy all data into StringBuilder
3. Copy the data to string using StringBuilder.ToString();

---

Instead you should write each row to a FileStream separately. There is no need to create the whole CSV in memory.

Even better, use a DataReader instead DataSet. That way you can read from database billions of records one by one a write the to a file one by one.

If you don't mind using an external library for CSV, I can recommend the most popular CsvHelper, which has no dependencies.

using (var writer = new FileWriter("test.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{       
    foreach (DataColumn dc in dt.Columns)
    {           
        csv.WriteField(dc.ColumnName);
    }
    csv.NextRecord();
    
    foreach (DataRow dr in dt.Rows)
    {           
        foreach (DataColumn dc in dt.Columns)
        {
            csv.WriteField(dr[dc]);
        }
        csv.NextRecord();
    }

    writer.ToString().Dump();
}

Dicta

Answer 19

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.

Smut