How to export a DataTable to a .CSV file
A .CSV file is the easiest and most common way of transferring data in a table format from one system, normally a spreadsheet to another system, again, normally a spreadsheet. A lot of spreadsheets can read formats other than their own, e.g. Excel can read Lotus 123 dataformat and vice versa.
If you're writing a system where you want to import into a spreadsheet from your home grown application, you're most likely going to need to use the .CSV file format because its easy to code. Using .CSV, you won't need third party controls or any format knowledge.
For this example, we are going to Export from a SQL Server database to a .CSV using Datatables. You can alter the code to use DataReaders if yo so wish, its not too difficult but for now, its just DataTables. DataReaders are useful when you are exporting a large amount of data, otherwise DataTables are sufficient.
For this example, you are going to need two references if you don't already have them.
using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
Then use this function to write out the text to a file. The code will handle items which have commas in them so that they don't create unnecessary cells in a spreadsheet. You can amend the code to handle DataReaders if you need or simply convert a DataReader into a DataTable for simplicity.
The replace statements are there to ensure that values are encased in quotes so that values with a comma in them are handled correctly and not create separate cells in the row.
public static void WriteTable(string sDest, ref DataTable oTbl)
{
string sText = "";
StringBuilder oSB = new StringBuilder();
StreamWriter fsOut = new StreamWriter(sDest);
IEnumerable
oSB.AppendLine(string.Join(",", fields));
fsOut.WriteLine(oSB.ToString().Substring(0, oSB.ToString().Length - 1));
oSB = new System.Text.StringBuilder();
foreach (DataRow oDR in oTbl.Rows)
{
IEnumerable
string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
oSB.AppendLine(string.Join(",", TheFields));
}
fsOut.Write(oSB.ToString());
fsOut.Close();
}
Last Modified : June 2023