/// <summary>
/// Writes data from a data table to a delimited text file
/// </summary>
/// <param name="dataTable">The data table with the data to write</param>
/// <param name="outputFilename">Full path and filename for the output file</param>
/// <param name="delimiter">The delimiter to be used to separate data items in a given data row.</param>
/// <param name="includeHeaders">True if the column names from the data table should be included as headers in the output stream</param>
/// <param name="append">Determines whether data is to be appended to the file. If the file exists and append is false, the file is overwritten. If the file exists and append is true, the data is appended to the file. Otherwise, a new file is created.</param>
/// <param name="bgWorker">BackgroundWorker (may be null), in order to show progress</param>
/// <param name="e">Arguments from a BackgroundWorker (may be null), in order to support canceling</param>
/// <param name="reportingOption">Indicates how the BackgroundWorker should report progress</param>
public static void DataTableToDelimitedFile(DataTable dataTable, string outputFilename, string delimiter, bool includeHeaders, bool append, BackgroundWorker bgWorker, DoWorkEventArgs e, BackgroundWorkerReportingOptions reportingOption)
{
var formatOptions = new DelimitedFormatOptions {
Append = append, Delimiter = delimiter, IncludeHeaders = includeHeaders
};
DataTableToDelimitedFile(dataTable, outputFilename, formatOptions, bgWorker, e, reportingOption);
}
/// <summary>
/// Writes data from a data table to a delimited text file
/// </summary>
/// <param name="dataTable">The data table with the data to write</param>
/// <param name="outputFilename">Full path and filename for the output file</param>
/// <param name="formatOptions">Delimited text formatting options</param>
/// <param name="bgWorker">BackgroundWorker (may be null), in order to show progress</param>
/// <param name="e">Arguments from a BackgroundWorker (may be null), in order to support canceling</param>
/// <param name="reportingOption">Indicates how the BackgroundWorker should report progress</param>
private static void DataTableToDelimitedFile(DataTable dataTable, string outputFilename, DelimitedFormatOptions formatOptions, BackgroundWorker bgWorker = null, DoWorkEventArgs e = null, BackgroundWorkerReportingOptions reportingOption = BackgroundWorkerReportingOptions.None)
{
// Check that the folder exists
try
{
string parentFolder = Path.GetDirectoryName(outputFilename);
if (Directory.Exists(parentFolder) == false)
{
throw new Exception("The folder where the output file is to be written does not exist");
}
}
catch (Exception ex)
{
throw new Exception("Could not identify folder where output file should be written.\n" + ex.Message);
}
// Attempt to create the output file
try
{
TextWriter outputStream;
using (outputStream = new StreamWriter(outputFilename, formatOptions.Append) as TextWriter)
{
try
{
// Write the data
DataTableToStream(dataTable, outputStream, formatOptions, bgWorker, e, reportingOption);
}
catch (Exception ex)
{
throw new Exception("Could not write to file.\n" + ex.Message);
}
// Close the file
outputStream.Close();
}
}
catch (Exception ex)
{
throw new Exception("Could not create output file.\n" + ex.Message);
}
}
/// <summary>
/// Writes data from a data table to a text stream, formatted as delimited values
/// </summary>
/// <param name="dataTable">The data table with the data to write</param>
/// <param name="outputStream">The text stream to which the delimited data will be written</param>
/// <param name="formatOptions">The delimited text stream formatting options</param>
/// <param name="bgWorker">BackgroundWorker (may be null), in order to show progress</param>
/// <param name="e">Arguments from a BackgroundWorker (may be null), in order to support canceling</param>
/// <param name="reportingOption">Indicates how the BackgroundWorker should report progress</param>
private static void DataTableToStream(DataTable dataTable, TextWriter outputStream, DelimitedFormatOptions formatOptions, BackgroundWorker bgWorker, DoWorkEventArgs e, BackgroundWorkerReportingOptions reportingOption)
{
// Check that columns are present
int columnCount = dataTable.Columns.Count;
if (columnCount == 0)
{
return;
}
// Get the number of rows in the table
long totalSteps = 0;
long currentStep = 0;
int previousPercentComplete = 0;
// Background worker updates
if (bgWorker != null)
{
// Check for cancel
if (e != null && bgWorker.CancellationPending)
{
e.Cancel = true;
return;
}
// Report progress
if (bgWorker.WorkerReportsProgress && reportingOption != BackgroundWorkerReportingOptions.None)
{
if (reportingOption == BackgroundWorkerReportingOptions.UserStateAndProgress)
{
bgWorker.ReportProgress(0, "Reading data...");
}
else if (reportingOption == BackgroundWorkerReportingOptions.ProgressOnly)
{
bgWorker.ReportProgress(0);
}
totalSteps = dataTable.Rows.Count;
}
}
// Write the column headers
if (formatOptions.IncludeHeaders)
{
// Background worker updates
if (bgWorker != null)
{
// Check for cancel
if (e != null && bgWorker.CancellationPending)
{
e.Cancel = true;
return;
}
// Report progress
if (bgWorker.WorkerReportsProgress && reportingOption != BackgroundWorkerReportingOptions.None)
{
if (reportingOption == BackgroundWorkerReportingOptions.UserStateAndProgress)
{
bgWorker.ReportProgress(0, "Reading column headers...");
}
else if (reportingOption == BackgroundWorkerReportingOptions.ProgressOnly)
{
bgWorker.ReportProgress(0);
}
totalSteps = dataTable.Rows.Count;
}
}
outputStream.Write("\"" + String.Join(formatOptions.Delimiter, citationBg) + "\"");
outputStream.WriteLine();
outputStream.Write("Citation:,");
outputStream.Write("\"" + String.Join(formatOptions.Delimiter, citation) + "\"");
outputStream.WriteLine();
outputStream.WriteLine();
// Write each column name from the data table
for (int i = 0; i < columnCount; i++)
{
var item = FormatDataItem(dataTable.Columns[i].ColumnName, formatOptions.Delimiter);
if (i > 0)
{
outputStream.Write(formatOptions.Delimiter + item);
}
else
{
outputStream.Write(item);
}
}
outputStream.Write(Environment.NewLine);
}
//date time column index
int dateTimeColumnIndex = -1;
for (int i = 0; i < columnCount; i++)
{
if (dataTable.Columns[i].DataType == typeof(DateTime))
{
dateTimeColumnIndex = i;
break;
}
}
//set correct culture info - decimal point formatting option
CultureInfo originalCulture = Thread.CurrentThread.CurrentCulture;
if (formatOptions.UseInvariantCulture)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
}
// Write the data
foreach (DataRow row in dataTable.Rows)
{
// Background worker updates
if (bgWorker != null)
{
// Check for cancel
if (e != null && bgWorker.CancellationPending)
{
e.Cancel = true;
return;
}
// Report progress
if (bgWorker.WorkerReportsProgress && reportingOption != BackgroundWorkerReportingOptions.None)
{
currentStep++;
var percentComplete = (int)(100 * currentStep / totalSteps);
if (percentComplete > previousPercentComplete)
{
if (reportingOption == BackgroundWorkerReportingOptions.UserStateAndProgress)
{
bgWorker.ReportProgress(percentComplete, "Writing line " + currentStep + " of " + totalSteps + "...");
}
else if (reportingOption == BackgroundWorkerReportingOptions.ProgressOnly)
{
bgWorker.ReportProgress(percentComplete);
}
previousPercentComplete = percentComplete;
}
}
}
// write data for each row
for (int i = 0; i < columnCount; i++)
{
var rowValue = row[i] ?? String.Empty;
string item;
if (i != dateTimeColumnIndex)
{
item = FormatDataItem(rowValue.ToString(), formatOptions.Delimiter);
}
else if (!formatOptions.UseShortDateFormat)
{
item = FormatDataItem((Convert.ToDateTime(rowValue)).ToString(formatOptions.DateTimeFormat), formatOptions.Delimiter);
}
else
{
item = FormatDataItem((Convert.ToDateTime(rowValue)).ToString("yyyy-MM-dd"), formatOptions.Delimiter);
}
if (i > 0)
{
outputStream.Write(formatOptions.Delimiter + item);
}
else
{
outputStream.Write(item);
}
}
outputStream.Write(System.Environment.NewLine);
}
//reset the culture info
if (Thread.CurrentThread.CurrentCulture != originalCulture)
{
Thread.CurrentThread.CurrentCulture = originalCulture;
}
// Background worker updates
if (bgWorker != null)
{
// Check for cancel
if (e != null && bgWorker.CancellationPending)
{
e.Cancel = true;
return;
}
// Report progress
if (bgWorker.WorkerReportsProgress && reportingOption != BackgroundWorkerReportingOptions.None)
{
if (reportingOption == BackgroundWorkerReportingOptions.UserStateAndProgress)
{
bgWorker.ReportProgress(100, "All lines written");
}
else if (reportingOption == BackgroundWorkerReportingOptions.ProgressOnly)
{
bgWorker.ReportProgress(100);
}
}
}
}
/// <summary>
/// Writes data from a data table to a text stream, formatted as delimited values
/// </summary>
/// <param name="dataTable">The data table with the data to write</param>
/// <param name="outputStream">The text stream to which the delimited data will be written</param>
/// <param name="formatOptions">The delimited text stream formatting options</param>
public static void DataTableToStream(DataTable dataTable, TextWriter outputStream, DelimitedFormatOptions formatOptions)
{
DataTableToStream(dataTable, outputStream, formatOptions, null, null, BackgroundWorkerReportingOptions.None);
}
