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")
Currently rated 3.67 by 3 people
Rate Now!
Date Posted:
02 August 2007 23:41
Last Updated:
02 August 2007 23:49
Posted by:
Mikesdotnetting
Total Views to date:
18250
Comments
03 May 2010 02:35 from 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);
03 May 2010 07:41 from Mikesdotnetting
@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!
10 May 2010 04:12 from newbie
Can I just use WriteToTextFile("|", "vfile.txt") when tab delimeted?
11 May 2010 07:45 from Mikesdotnetting
@newbie
Your separator appears to be a pipe rather than a tab. Tabs are usually \t in C# or vbTab in VB.