/// <summary> /// Save the DataTable as a CSV file /// </summary> /// <param name="dt"></param> /// <param name="filename"></param> /// <param name="options"></param> public static void ExportCSV(DataTable dt, string filename, DelimiterImportOptions options) { bool writeHeader = options.UseHeader; string delimiter = options.Delimiter; bool useTextQualifier = options.UseTextQualifier; string qualifier = options.UseTextQualifier ? options.TextQualifierId : string.Empty; string newlineChar = options.NewlineChar; using (StreamWriter s = new StreamWriter(filename)) { if (writeHeader) { for (int i = 0; i < dt.Columns.Count; i++) { DataColumn dc = dt.Columns[i]; if (i > 0) { s.Write(delimiter); } s.Write("{0}{1}{0}", qualifier, MakeSafeColumnName(dc.ColumnName)); } s.Write(newlineChar); } for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; for (int j = 0; j < dt.Columns.Count; j++) { DataColumn dc = dt.Columns[j]; if (j > 0) { s.Write(delimiter); } s.Write("{0}{1}{0}", qualifier, MakeSafeColumnValue(dr[dc.ColumnName])); } s.Write(newlineChar); } s.Flush(); s.Close(); } }
private void btnImport_Click(object sender, EventArgs e) { const string OUT_TABLENAME = @"CSVImportTest"; const int MAX_BUFFER = 100; var start = DateTime.Now; try { var dci = csfSaveAs.DataConnectionInfo; if (dci.InputSource != InputSource.Database) { throw new ArgumentException("Please choose a database"); } var df = new DataFactory(dci.Provider, dci.ConnectionString); df.TestConnection(); if (df.Database.GetTables(OUT_TABLENAME, QueryFilter.Exact).Count >= 1) { df.ExecuteNonQuery("drop table " + OUT_TABLENAME); } int nr = 0; using (DataTable dt = new DataTable(OUT_TABLENAME)) { var options = new DelimiterImportOptions(","); foreach (var item in ImportManager.ImportRow(txtFilename.Value, options)) { nr++; #region Init Columns Definition if (nr == 1) { DataUtil.CopyColumns(item.Table.Columns, dt.Columns); } #endregion var dr = dt.NewRow(); DataUtil.CopyRow(item, dr); dt.Rows.Add(dr); if (nr % MAX_BUFFER == 0) { df.AppendTable(OUT_TABLENAME, dt); dt.Rows.Clear(); } } if (dt.Rows.Count > 0) { df.AppendTable(OUT_TABLENAME, dt); dt.Rows.Clear(); } } MessageBox.Show("Done Elapsed = " + DateUtil.ElapsedTime(start, DateTime.Now)); } catch (Exception ex) { FormUtil.WinException(ex); } }
public static IEnumerable <DataRow> ImportCSV2(string filename, DelimiterImportOptions options) { bool hasHeader = options.UseHeader; string delimiter = options.Delimiter; bool useTextQualifier = options.UseTextQualifier; string qualifier = options.UseTextQualifier ? options.TextQualifierId : string.Empty; int maxRows = options.ImportMaxRows; if (!File.Exists(filename)) { throw new FileNotFoundException(filename); } int nr = 0; int nf = 0; DataRow dr = null; using (DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(filename))) using (StreamReader r = new StreamReader(filename)) { while (!r.EndOfStream) { string buffer = r.ReadLine(); if (string.IsNullOrEmpty(buffer)) { continue; } List <string> fields = Split(buffer, delimiter, qualifier); nr++; #region Create Columns if (nr == 1) { nf = fields.Count; if (hasHeader) { ValidateHeaderField(fields); for (int i = 0; i < fields.Count; i++) { string name = MakeSafeColumnName(fields[i]); dt.Columns.Add(name); } } else { // make up a generic field names. for (int i = 0; i < fields.Count; i++) { dt.Columns.Add("Field" + (i + 1)); } } dr = dt.NewRow(); // skip this line if, and only if, the first row has header info if (hasHeader) { continue; } } #endregion #region attempt to fix splitted lines. if (fields.Count != nf) { while (fields.Count < nf && !r.EndOfStream) { string buf2 = r.ReadLine(); buffer += "\\n" + buf2; fields = Split(buffer, delimiter, qualifier); } if (fields.Count != nf) { throw new ApplicationException("inconsistent field count. (nf=[" + nf + "], fields.Count=[" + fields.Count + "], nr=[" + nr + "])"); } } #endregion #region Insert Data into columns for (int i = 0; i < fields.Count; i++) { dr[i] = fields[i]; } #endregion yield return(dr); if (maxRows > 0 && nr > maxRows) { break; } } r.Close(); } yield break; }