Exporting data to a CSV, tab delimited or other text format

3.4 (5 votes)

A question that often comes up in forums is how to export data to a CSV file, or other text format. Here's a method that takes data from a DataReader and writes it to a file.

[C#]

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;

public static void WriteToTextFile(string separator, string filename)
{
  string ConnStr = "Provider=Microsoft.Jet.OleDb.4.0;" +
				"Data Source=|DataDirectory|Northwind.mdb;";
  string query = "SELECT * FROM Customers";
  string sep = separator;

  StreamWriter sw = new StreamWriter(HttpContext.Current.Server.MapPath(filename));
  using (OleDbConnection Conn = new OleDbConnection(ConnStr))
  {
    using (OleDbCommand Cmd = new OleDbCommand(query, Conn))
    {
      Conn.Open();
      using (OleDbDataReader dr = Cmd.ExecuteReader())
      {
        int fields = dr.FieldCount - 1;
        while (dr.Read())
        {
          StringBuilder sb = new StringBuilder();
          for (int i = 0; i <= fields; i++)
          {
            if (i != fields)
            {
              sep = sep;
            }
            else
            {
              sep = "";
            }
            sb.Append(dr[i].ToString() + sep);

          }
          sw.WriteLine(sb.ToString());
        }
      }
    }
  }
}
[VB]

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Text
Imports System.Web

Public Shared Sub WriteToTextFile(ByVal separator As String, ByVal filename As String)
  Dim ConnStr As String = "Provider=Microsoft.Jet.OleDb.4.0;" + 
          "Data Source=|DataDirectory|Northwind.mdb;"
  Dim query As String = "SELECT * FROM Customers"
  Dim sep As String = separator

  Dim sw As New StreamWriter(HttpContext.Current.Server.MapPath(filename))
  Using Conn As New OleDbConnection(ConnStr)
    Using Cmd As New OleDbCommand(query, Conn)
    Conn.Open()
      Using dr As OleDbDataReader = Cmd.ExecuteReader()
        Dim fields As Integer = dr.FieldCount - 1
        While dr.Read()
          Dim sb As New StringBuilder()
          Dim i As Integer = 0
          While i <= fields
            If i <> fields Then
              sep = sep
            Else
              sep = ""
            End If
            sb.Append(dr(i).ToString() + sep)
            i += 1
          End While
          sw.WriteLine(sb.ToString())
        End While
      End Using
    End Using
  End Using
End Sub

And in both cases, the method is called by simply passing the separator and filename in as strings:

WriteToTextFile(",", "vfile.txt")

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 51483

4 Comments

- Larry Grimes

Why would you EVER write, "sep = sep;"?

C#: if (i == fields) sep = "";VB: If (i = fields) Then sep = ""

NOTE: I ALWAYS use parens to denote identities, it REALLY helps clarify code, even in "If" statements and even if it doesn't seem necessary. It sure helps someone else reading your code!

Maybe, the ONLY time I could see it used is in tertiary commands:

sep = ((i == fields) ? "" : sep);

- Mike

@Larry

Thanks for your comments. You are quite right, of course. At some stage, I might find the time to go over all these older posts and improve the code. A lot of them could do with improvement!

- newbie

Can I just use WriteToTextFile("|", "vfile.txt") when tab delimeted?

- Mike

@newbie

Your separator appears to be a pipe rather than a tab. Tabs are usually \t in C# or vbTab in VB.

Recent Comments

dave 20/08/2016 14:57
In response to ASP.NET Web Pages vNext or Razor Pages
Do SimplemembershipProvider in viewpages is supported?...

Steven 18/08/2016 04:40
In response to Entity Framework Code First and Stored Procedures
Can you provide the directives (using statements) you're using for EF7 example?...

yousaid 17/08/2016 22:08
In response to ASP.NET Web Pages vNext or Razor Pages
Increasingly, learning a Microsoft tool is no longer worth the return on investment. Too many tools...

jared 12/08/2016 05:54
In response to ASP.NET Web Pages vNext or Razor Pages
hi mike, just for clarification, is viewpages something different from webpages? is webpages still...

Jocke 08/08/2016 20:37
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Good post! If this was to be implemented in a CMS where users can change the view files, how would I...

cyrus 05/08/2016 19:49
In response to ASP.NET Web Pages vNext or Razor Pages
I think adding these features to webpages make it complicated. msft forget webpages goal so we move...

Curt Smith 27/07/2016 20:38
In response to ASP.NET Web Pages vNext or Razor Pages
I am only slightly disappointed to hear that WebMatrix is officially dead, because I suspected this...

Darshan Raj L G 27/07/2016 13:20
In response to Implementing SQL Server Full-Text Search In An ASP.NET MVC Web Application With Entity Framework
I though it would be more helpful for somebody who wants to work with Entity Framework... please EF...

Satyabrata 25/07/2016 08:09
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Very Interesting!!...

Jerrie Pelser 23/07/2016 05:08
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Very cool concept Mike!...