/// <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); }