DataTable to CSV with custom string representation

Question

I want to convert a DataTable to a CSV string lines, and I want to have the flexibility to code the string representation of each column (Example: I may want Date values in a specific string format, etc.)

I have seen some generic methods to convert a DataTable to CSV here: c# datatable to csv

But none of the methods address custom string representation. Any suggestions?

Not clear what you are trying to achieve, can you give few concrete example scenarios please? — jimjim, Oct 03 '16 at 22:33

score 1 · Answer 1 · answered Oct 03 '16 at 23:26

Just check the column type and specify the format you want, if it is a date.

DataTable dt = ...

// Your custom date format.
string format = "yyyy-MM-dd";

var sb = new StringBuilder();

foreach (DataRow row in dt.Rows)
{
    sb.AppendLine(string.Join(",", row.ItemArray.Select(item =>
        item is DateTime ? ((DateTime)item).ToString(format) : item.ToString()
    )));
}

File.WriteAllText("test.csv", sb.ToString());

score 1 · Answer 2 · edited May 23 '17 at 12:33

Borrowing from c# datatable to csv as mentioned, I would create an extension method that takes two format strings as parameters and looks like this:

    static class MyExtensions
{
    public static string ToCSV(this DataTable dataTable, string rowFormat, string headFormat)
    {
        StringBuilder sb = new StringBuilder();

        IEnumerable<string> columnNames = dataTable.Columns.Cast<DataColumn>().
                                          Select(column => column.ColumnName);
        sb.AppendFormat(headFormat, columnNames.ToArray<string>());

        foreach (DataRow row in dataTable.Rows)
        {
            sb.AppendLine();
            sb.AppendFormat(rowFormat, row.ItemArray);
        }
        return sb.ToString();
    }

}

Then call it like this:

using (DataTable dt = new DataTable("Test"))
        {
            dt.Columns.Add(new DataColumn("id", typeof(int)));
            dt.Columns.Add(new DataColumn("value", typeof(double)));
            dt.Columns.Add(new DataColumn("dated", typeof(DateTime)));
            dt.Rows.Add(new Object[] { 1, 2.3, DateTime.Now });
            Console.WriteLine(dt.ToCSV("{0}|\"{1}\"|{2:d}", "{0}|{1}|{2}"));
            Console.ReadKey();
        }

To produce this:

id|value|dated
1|"2.3"|04/10/2016

This gives you the flexibility to define the structure of your CSV precisely.

If you don't know the structure, then this won't help.

DataTable to CSV with custom string representation

2 Answers2