Click here to Skip to main content
11,504,184 members (73,378 online)
Click here to Skip to main content

Writing a DataTable to a CSV file

, 8 Oct 2013 CPOL 54.9K 20
Rate this:
Please Sign up or sign in to vote.
A quick way to write the contents of a DataTable to an RFC 4180-compliant CSV file.

Introduction

There are many times when we need to export the contents of a DataTable to a format compatible with spreadsheet applications, such as Microsoft Excel or Apple Numbers.  This quick little class will write the contents of a DataTable to a TextWriter instance, whether this is a physical file or network stream. 

Here's the class itself:  

// C# Code
public static class Rfc4180Writer 
{
    public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders) 
    {
        if (includeHeaders) {
            List<string> headerValues = new List<string>();
            foreach (DataColumn column in sourceTable.Columns) {
                headerValues.Add(QuoteValue(column.ColumnName));
            }

            writer.WriteLine(String.Join(",", headerValues.ToArray()));
        }

        string[] items = null;
        foreach (DataRow row in sourceTable.Rows) {
            items = row.ItemArray.Select(o => QuoteValue(o.ToString())).ToArray();
            writer.WriteLine(String.Join(",", items));
        }

        writer.Flush();
    }

    private static string QuoteValue(string value) 
    {
        return String.Concat("\"", value.Replace("\"", "\"\""), "\"");
    }
} 
'VB Code
Public Class Rfc4180Writer
    Public Shared Sub WriteDataTable(ByVal sourceTable As DataTable, ByVal writer As TextWriter, ByVal includeHeaders As Boolean)
        If (includeHeaders) Then
            Dim headerValues As List(Of String) = New List(Of String)()
            For Each column As DataColumn In sourceTable.Columns
                headerValues.Add(QuoteValue(column.ColumnName))
            Next
        End If

        Dim items() As String = Nothing
        For Each row As DataRow In sourceTable.Rows
            items = row.ItemArray.Select(Function(obj) QuoteValue(obj.ToString())).ToArray()
            writer.WriteLine(String.Join(",", items)
        Next

        writer.Flush()
    End Sub

    Private Shared Function QuoteValue(ByVal value As String) As String
        Return String.Concat("""", value.Replace("""", """"""), """")
    End Function
End Class 

Using the code

Making use of the class is straightforward in a number of applications.  For the desktop world, here's a console app demonstrating the use via StreamWriter:

// C# Code
public static class Program {
    public static void Main() {
        DataTable sourceTable = new DataTable();

        sourceTable.Columns.AddRange(new DataColumn[] {
            new DataColumn("ID", typeof(Guid)),
            new DataColumn("Date", typeof(DateTime)),
            new DataColumn("StringValue", typeof(string)),
            new DataColumn("NumberValue", typeof(int)),
            new DataColumn("BooleanValue", typeof(bool))
        });

        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);

        using (StreamWriter writer = new StreamWriter("C:\\Temp\\dump.csv")) {
            Rfc4180Writer.WriteDataTable(sourceTable, writer, true);
        }
    }
} 
 ' VB Code
Public Class Program
    Public Shared Sub Main()
        Dim sourceTable As DataTable = New DataTable()
        
        sourceTable.Columns.AddRange(New DataColumn() {
            New DataColumn("ID", GetType(Guid)),
            New DataColumn("Date", GetType(Date)),
            New DataColumn("StringValue", GetType(String)),
            New DataColumn("NumberValue", GetType(Integer)),
            New DataColumn("BooleanValue", GetType(Boolean))
        })
        
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, True)
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, False)
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, True)
        
        Using writer As StreamWriter = New StreamWriter("C:\Temp\dump.csv")
            Rfc4180Writer.WriteDataTable(sourceTable, writer, True)
        End Using
    End Sub
End Class  

For the ASP.NET MVC world, specifically MVC 3 or later, the demonstration isn't much different than the above example except the result is returned as a FilePathResult instance on a controller action:

// C# Code
public class ReportController : Controller
{
    [HttpGet()]
    public ActionResult Export()
    {
        DataTable sourceTable = new DataTable();

        sourceTable.Columns.AddRange(new DataColumn[] {
            new DataColumn("ID", typeof(Guid)),
            new DataColumn("Date", typeof(DateTime)),
            new DataColumn("StringValue", typeof(string)),
            new DataColumn("NumberValue", typeof(int)),
            new DataColumn("BooleanValue", typeof(bool))
        });

        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);

        byte[] outputBuffer = null;

        using (MemoryStream tempStream = new MemoryStream()) {
            using (StreamWriter writer = new StreamWriter(tempStream)) {
                Rfc4180Writer.WriteDataTable(sourceTable, writer, true);
            }

            outputBuffer = tempStream.ToArray();
        }

        return File(outputBuffer, "text/csv", "export.csv");
    }
} 
' VB Code
Public Class ReportController
    Inherits Controller

    <HttpGet()> _
    Public Function Export() As ActionResult
        Dim sourceTable As DataTable = New DataTable()

        sourceTable.Columns.AddRange(New DataColumn() {
            New DataColumn("ID", GetType(Guid)),
            New DataColumn("Date", GetType(Date)),
            New DataColumn("StringValue", GetType(String)),
            New DataColumn("NumberValue", GetType(Integer)),
            New DataColumn("BooleanValue", GetType(Boolean))
        })

        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, True)
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, False)
        sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, True)

        Dim outputBuffer() As Byte = Nothing
        Using tempStream As MemoryStream = New MemoryStream()
            Using writer As StreamWriter = New StreamWriter(tempStream)
                Rfc4180Writer.WriteDataTable(sourceTable, writer, True)
            End Using

            outputBuffer = tempStream.ToArray()
        End Using

        Return File(outputBuffer, "text/csv", "export.csv")
    End Function

End Class

Points of Interest

If you're not familiar with the original RFC, take a second to read it over at: http://tools.ietf.org/html/rfc4180.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Thomas Corey
Engineer Intrigue Deviation
United States United States
No Biography provided

Comments and Discussions

 
Questionwell written!!!! Pin
Member 108866888-Dec-14 19:04
memberMember 108866888-Dec-14 19:04 
QuestionForgot To Add Row Headers Pin
Member 1126654226-Nov-14 6:49
memberMember 1126654226-Nov-14 6:49 
QuestionWriteline missing for header in VB Pin
Jason Timmins28-Apr-14 3:07
memberJason Timmins28-Apr-14 3:07 
AnswerRe: Writeline missing for header in VB Pin
raghavendran from Tiruvallur16-May-15 2:12
memberraghavendran from Tiruvallur16-May-15 2:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 8 Oct 2013
Article Copyright 2013 by Thomas Corey
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid