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

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")

Date Posted: Thursday, August 2, 2007 11:41 PM
Last Updated: Thursday, August 2, 2007 11:49 PM
Posted by: Mikesdotnetting
Total Views to date: 34735

4 Comments

Monday, May 3, 2010 2:35 AM - 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);

Monday, May 3, 2010 7:41 AM - 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!

Monday, May 10, 2010 4:12 AM - newbie

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

Tuesday, May 11, 2010 7:45 AM - Mike

@newbie

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

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Manas 11/27/2014 5:30 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, Thank you for awesome article. My concern is it might impact website if we use or is...

priya 11/26/2014 6:50 PM
In response to Create PDFs in ASP.NET - getting started with iTextSharp
very nice.....its save my time...

ransems 11/24/2014 12:29 AM
In response to Adding A Controller
Love the article. I dislike that the world thinks c# articles are the way to go. Love the VB, keep...

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...