Click here to Skip to main content
13,143,459 members (31,599 online)
Click here to Skip to main content
Add your own
alternative version

Stats

136.9K views
57 bookmarked
Posted 8 Oct 2013

Writing a DataTable to a CSV File

, 8 Sep 2017
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

using System.Data;
using System.IO;
using System.Linq;

public static class Rfc4180Writer 
{
    public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders) 
    {
        if (includeHeaders) {
            IEnumerable<String> headerValues = sourceTable.Columns
                .OfType<DataColumn>()
                .Select(column => QuoteValue(column.ColumnName));
                
            writer.WriteLine(String.Join(",", headerValues));
        }

        IEnumerable<String> items = null;

        foreach (DataRow row in sourceTable.Rows) {
            items = row.ItemArray.Select(o => QuoteValue(o?.ToString() ?? String.Empty));
            writer.WriteLine(String.Join(",", items));
        }

        writer.Flush();
    }

    private static string QuoteValue(string value) 
    {
        return String.Concat("\"", 
        value.Replace("\"", "\"\""), "\"");
    }
} 
'VB Code

Imports System.Data
Imports System.IO
Imports System.Linq

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 IEnumerable(Of String) = sourceTable.Columns
                .OfType(Of DataColumn)
                .Select(Function(column) QuoteValue(column.ColumnName))

            writer.WriteLine(String.Join(",", headerValues))
        End If

        Dim items As IEnumerbale(Of String) = Nothing
        For Each row As DataRow In sourceTable.Rows
            items = row.ItemArray.Select(Function(obj) QuoteValue(If(obj?.ToString(), String.Empty)))
            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 Robert Half Technology
United States United States
Polyglot, architect, and general all-around nerd.

You may also be interested in...

Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
Carlven Lao14-Sep-17 5:32
memberCarlven Lao14-Sep-17 5:32 
PraiseThank you for supporting VB Pin
Jeff Bowman11-Sep-17 9:56
professionalJeff Bowman11-Sep-17 9:56 
GeneralRe: Thank you for supporting VB Pin
Thomas Corey17-Sep-17 23:43
memberThomas Corey17-Sep-17 23:43 
BugCommas in data Pin
gggustafson11-Sep-17 6:43
professionalgggustafson11-Sep-17 6:43 
GeneralRe: Commas in data Pin
Thomas Corey17-Sep-17 23:42
memberThomas Corey17-Sep-17 23:42 
GeneralRe: Commas in data Pin
gggustafson18-Sep-17 4:19
professionalgggustafson18-Sep-17 4:19 
GeneralRe: Commas in data Pin
Thomas Corey23hrs 53mins ago
memberThomas Corey23hrs 53mins ago 
GeneralRe: Commas in data Pin
gggustafson23hrs 8mins ago
professionalgggustafson23hrs 8mins ago 
QuestionASP.Net Core Version? Pin
MacroDaveW19-Jul-17 5:02
memberMacroDaveW19-Jul-17 5:02 
AnswerRe: ASP.Net Core Version? Pin
Thomas Corey8-Sep-17 1:28
memberThomas Corey8-Sep-17 1:28 
PraiseGreat Pin
James196817-Jul-17 7:15
memberJames196817-Jul-17 7:15 
QuestionConvert to tsv Pin
Lokesh Zende7-May-17 19:46
professionalLokesh Zende7-May-17 19:46 
QuestionNull Values Pin
Juzer8-Jun-16 8:44
memberJuzer8-Jun-16 8:44 
AnswerRe: Null Values Pin
tolsen6418-Jul-17 14:58
membertolsen6418-Jul-17 14:58 
SuggestionQuick syntax correction Pin
Robert Beveridge18-Sep-15 10:48
memberRobert Beveridge18-Sep-15 10:48 
Questionwell written!!!! Pin
Member 108866888-Dec-14 19:04
memberMember 108866888-Dec-14 19:04 
AnswerRe: well written!!!! Pin
tolsen6418-Jul-17 15:02
membertolsen6418-Jul-17 15:02 
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    Praise Praise    Rant Rant    Admin Admin   

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 8 Sep 2017
Article Copyright 2013 by Thomas Corey
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid