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