Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Tip: export to CSV using ADO.NET

A friend of mine was telling me about a bug he found in one of his applications caused by a simple lack of escaping quotes when producing CSV files. It immediately reminded me of an old trick in .NET.

If you really want, you can create CSV files using ADO.NET and OLE-DB (or ODBC.) I wouldn't necessarily recommend this approach but it is definitely one of those things that when you see for the first time you go "I never thought this could be done this way."

The idea is simple — open an OLE-DB connection using the Jet OLE-DB provider, create a table (which is really just a file) and insert rows in that table (or lines in that file.)

//create a temp directory for the CSV output
string tempFile = Path.GetTempFileName();
File.Delete(tempFile);
tempFile = Path.GetFileNameWithoutExtension(tempFile);
string dir = Path.Combine(Path.GetTempPath(),  tempFile );
Directory.CreateDirectory(dir);
string csvFile =  Path.Combine(dir, "data.csv");

string cnStr = 
    "Provider=Microsoft.Jet.OLEDB.4.0;" + 
    "Extended Properties='text;HDR=Yes;FMT=Delimited';" + 
    "Data Source=" + dir + ";";

using (var cn = new OleDbConnection(cnStr))
{
    cn.Open();

    //define the file layout (a.k.a. the table)
    var cmd = new OleDbCommand(
        "CREATE TABLE data.csv (CharColumn VARCHAR(30), IntColumn INT)", cn);
    cmd.ExecuteNonQuery();

    //start pumping data
    cmd = new OleDbCommand(
        "INSERT INTO data.csv (CharColumn, IntColumn) VALUES (?, ?)", cn);

    //in a more realistic example this part
    // would be inside some type of loop

    //1st record
    cmd.Parameters.AddWithValue("?", "11111111");
    cmd.Parameters.AddWithValue("?", 1234);
    cmd.ExecuteNonQuery();

    //2nd record
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("?", "22222\"22222");
    cmd.Parameters.AddWithValue("?", 6789);
    cmd.ExecuteNonQuery();

    //etc...
}

//read the csv formatted data
string csv = File.ReadAllText(csvFile);

//cleanup
Directory.Delete(dir, true);

Console.WriteLine("Result:");
Console.WriteLine("--------------------");
Console.WriteLine(csv);
Console.ReadLine();

The output of this will be as follows. Note the escaped double quote in the last line.

"CharColumn","IntColumn"
"11111111",1234
"22222""22222",6789

Of course, you can also read from a CSV file with simple SELECT statements against the file. You can let ADO.NET take care of all your typical CSV tasks.

Fabrice Marguerie wrote about this same topic a long time ago (check the comments and links too).

You may also want to check a more structured approach to the Export to CSV problem by way of the FileHelpers Library.


Posted 09-17-2008 6:14 PM by sergiopereira
Filed under: ,

[Advertisement]

Comments

Arjan`s World » LINKBLOG for September 18, 2008 wrote Arjan`s World » LINKBLOG for September 18, 2008
on 09-18-2008 4:35 PM

Pingback from  Arjan`s World    » LINKBLOG for September 18, 2008

vinay wrote re: Tip: export to CSV using ADO.NET
on 02-13-2009 8:34 AM

great piece of code

thank you

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)