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.
