Assuming that you want to store the column-name in the first and the types in the second line and the data begins in the third line, you could use following code. Sample data:
DataTable tblExport = new DataTable();
tblExport.Columns.Add("ID", typeof(int));
tblExport.Columns.Add("Name", typeof(string));
tblExport.Columns.Add("DateofBirth", typeof(DateTime)).AllowDBNull = false;
tblExport.Columns.Add("DateofDeath", typeof(DateTime)).AllowDBNull = true;
tblExport.Rows.Add(1, "Tim", new DateTime(1973, 7, 9), DBNull.Value);
tblExport.Rows.Add(2, "Jim", new DateTime(1953, 3, 19), new DateTime(2011, 1, 2));
tblExport.Rows.Add(3, "Toby", new DateTime(1983, 4, 23), DBNull.Value);
Since you need to convert all values to string with value.ToString i'm changing the culture to InvariantCulture at the beginning to force a specific DateTime format, store the old so that you can enable it again at the end. I hope the code is self-explaining:
var oldCulture = CultureInfo.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
write DataTable to csv-file
string delimiter = "\t"; // tab separated
StringBuilder sb = new StringBuilder();
// first line column-names
IEnumerable<string> columnNames = tblExport.Columns.Cast<DataColumn>()
.Select(column => column.ColumnName);
sb.AppendLine(string.Join(delimiter, columnNames));
// second line column-types
IEnumerable<string> columnTypes = tblExport.Columns.Cast<DataColumn>()
.Select(column => column.DataType.ToString());
sb.AppendLine(string.Join(delimiter, columnTypes));
// rest: table data
foreach (DataRow row in tblExport.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(delimiter, fields));
}
string path = @"C:\Temp\Testfile.csv";
File.WriteAllText(path, sb.ToString());
read csv-file into DataTable
string[] lines = File.ReadAllLines(path);
string[] columns = lines[0].Split(new[] { delimiter }, StringSplitOptions.None);
string[] types = lines[1].Split(new[] { delimiter }, StringSplitOptions.None);
DataTable tblImport = new DataTable();
for (int i = 0; i < columns.Length; i++)
{
string colName = columns[i];
string typeName = types[i];
tblImport.Columns.Add(colName, Type.GetType(typeName));
}
// import data
// use a typeValueConverter dictionary to convert values:
var typeValueConverter = new Dictionary<Type, Func<string, object>> {
{ typeof(DateTime), value => value.TryGetDateTime(null, null) },
{ typeof(Decimal), value => value.TryGetDecimal(null) },
{ typeof(int), value => value.TryGetInt32(null) },
};
foreach (string line in lines.Skip(2))
{
string[] fields = line.Split(new[]{ delimiter }, StringSplitOptions.None);
DataRow r = tblImport.Rows.Add(); // already added at this point
for (int i = 0; i < tblImport.Columns.Count; i++)
{
DataColumn col = tblImport.Columns[i];
string rawValue = fields[i];
object val = rawValue;
if (typeValueConverter.ContainsKey(col.DataType))
val = typeValueConverter[col.DataType](rawValue);
else if (col.DataType != typeof(string) && string.IsNullOrEmpty(rawValue))
val = DBNull.Value;
r.SetField(col, val);
}
}
System.Threading.Thread.CurrentThread.CurrentCulture = oldCulture;
Of course you should separate both in two methods, one for exporting and one for importing.
I've used my extensions method TryGetDateTime, TryGetDecimal and TryGetInt32 which parses strings to DateTime?,Decimal? and int?(null if it couldn't be parsed). They are especially handy in LINQ queries:
public static DateTime? TryGetDateTime(this string item, DateTimeFormatInfo dfi, params string[] allowedFormats)
{
if (dfi == null) dfi = DateTimeFormatInfo.InvariantInfo;
DateTime dt;
bool success;
if(allowedFormats == null)
success = DateTime.TryParse(item, dfi, DateTimeStyles.None, out dt);
else
success = DateTime.TryParseExact(item, allowedFormats, dfi, DateTimeStyles.None, out dt);
if (success) return dt;
return null;
}
public static decimal? TryGetDecimal(this string item, IFormatProvider formatProvider = null, NumberStyles nStyles = NumberStyles.Any)
{
if (formatProvider == null) formatProvider = NumberFormatInfo.InvariantInfo;
decimal d = 0m;
bool success = decimal.TryParse(item, nStyles, formatProvider, out d);
if (success)
return d;
else
return null;
}
public static int? TryGetInt32(this string item, IFormatProvider formatProvider = null, NumberStyles nStyles = NumberStyles.Any)
{
if (formatProvider == null) formatProvider = NumberFormatInfo.InvariantInfo;
int i = 0;
bool success = int.TryParse(item, nStyles, formatProvider, out i);
if (success)
return i;
else
return null;
}
