Storing a DataTable in a Flatfile

Question

This is the first time I have done any sort of work with flat files. I need this to be a plain txt file NOT XML.

I have written the following opting for a comma delimited format.

    public static void DataTableToFile(string fileLoc, DataTable dt)
    {
        StringBuilder str = new StringBuilder();
        // get the column headers
        foreach (DataColumn c in dt.Columns)
        {
            str.Append(c.ColumnName.ToString() + ",");
        }
        str.Remove(str.Length-1, 1);
        str.AppendLine();
        // write the data here
        foreach (DataRow dr in dt.Rows)
        {
            foreach (var field in dr.ItemArray)
            {
                str.Append(field.ToString() + ",");
            }
            str.Remove(str.Length-1, 1);
            str.AppendLine();
        }
        try
        {
            Write(fileLoc, str.ToString());
        }
        catch (Exception ex)
        {
            //ToDO:Add error logging
        }
    }

My question is: Can i do this better or faster? And str.Remove(str.Length-1, 1); is there to remove the last , which is the only way I could think of. Any suggestions?

You're essentially trying to write a data table to CSV format. See http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv — Muhammad Hasan Khan, Jun 05 '12 at 11:16

Nikhil Agrawal · Accepted Answer · 2012-06-05T11:39:24.133

3

Use

public static void DataTableToFile(string fileLoc, DataTable dt)
{
    StringBuilder str = new StringBuilder();

    // get the column headers
    str.Append(String.Join(",", dt.Columns.Cast<DataColumn>()
                                      .Select(col => col.ColumnName)) + "\r\n");

    // write the data here
    dt.Rows.Cast<DataRow>().ToList()
           .ForEach(row => str.Append(string.Join(",", row.ItemArray) + "\r\n"));

    try
    {
        Write(fileLoc, str.ToString());
    }
    catch (Exception ex)
    {
        //ToDO:Add error logging
    }
}

edited Jun 05 '12 at 11:39

answered Jun 05 '12 at 11:15

Nikhil Agrawal

47,018
22
121
208

But this writes only the column names, not the data. – Tim Schmelter Jun 05 '12 at 11:19
@Tim I believe it is meant to be representative of a *technique*. You would obviously use something very similar on a line-by-line basis. – Marc Gravell Jun 05 '12 at 11:20
@TimSchmelter: Exactly MarcGarvll. He wants a faster code not a rent-a-coder. – Nikhil Agrawal Jun 05 '12 at 11:23

score 3 · Answer 2 · answered Jun 05 '12 at 11:17

The key point would be: there is no need to construct this in memory with a StringBuilder - you should instead be writing to a file via something like StreamWriter, i.e. via File.CreateText. The API is similar to StringBuilder, but you shouldn't try to remove - instead, don't add - i.e.

bool first = true;
foreach(...blah...) {
    if(first) { first = false; }
    else { writer.Write(','); }
    ... write the data ...
}

As another consideration: CSV is not just a case of adding commas. You need to think about quoted text (for data with , in), and multi-line data. Unless the data is very very simple. You might also want to make the format more explicit than just .ToString(), which is very culture-sensitive. The classic example would be large parts of Europe that use , as the decimal character, thus "CSV" often uses a different separator, to avoid having to quote everything. If the choice is available, personally I'd always use TSV instead of CSV - less problematic (in theory, although you still need to handle data with tabs in).

Storing a DataTable in a Flatfile

2 Answers2