How to Convert a DataTable to a CSV File in C#

If you are working with data in a DataTable object in C#, you may need to convert it to a CSV (Comma-Separated Values) file format for various reasons. A CSV file is a simple text file where each line represents a row of data, and the values are separated by commas. In this article, I will show you how to convert a DataTable to a CSV file using C#.

What is a DataTable?

Before we dive into the conversion process, let’s quickly understand what a DataTable is. In C#, a DataTable is an in-memory representation of a table with rows and columns. It is part of the System.Data namespace and provides a convenient way to store and manipulate tabular data.

How to Convert a DataTable to a CSV File

To convert a DataTable to a CSV file, we can follow these steps:

1. Create a StringBuilder object to store the CSV content.
2. Get the column names from the DataTable and append them to the StringBuilder, separated by commas.
3. Iterate over each row in the DataTable.
4. Get the field values for each row and append them to the StringBuilder, separated by commas.
5. Write the contents of the StringBuilder to a CSV file.

Here’s the code snippet that demonstrates the conversion process:

StringBuilder sb = new StringBuilder();

// Step 2: Get column names
string[] columnNames = dt.Columns.Cast<DataColumn>()
                                  .Select(column => column.ColumnName)
                                  .ToArray();
sb.AppendLine(string.Join(",", columnNames));

// Step 3 and 4: Iterate over rows and get field values
foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString())
                                    .ToArray();
    sb.AppendLine(string.Join(",", fields));
}

// Step 5: Write to CSV file
File.WriteAllText("test.csv", sb.ToString());

In the above code, dt represents the DataTable object that you want to convert to a CSV file. The StringBuilder class is used to efficiently build the CSV content. We first get the column names from the DataTable and append them to the StringBuilder. Then, we iterate over each row, get the field values, and append them to the StringBuilder. Finally, we write the contents of the StringBuilder to a CSV file named “test.csv”.

Handling Special Characters in CSV Fields

If your DataTable contains fields with special characters like commas or double quotes, you need to handle them properly in the CSV file. One way to handle this is by enclosing each field value in double quotes and escaping any double quotes within the field value. Here’s an example of how to modify the code to handle special characters:

foreach (DataRow row in dt.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field => 
      string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
    sb.AppendLine(string.Join(",", fields));
}

In the above code, we use the string.Concat method to add double quotes around each field value. We also replace any double quotes within the field value with two double quotes (escaping). This ensures that the CSV file is properly formatted and can handle special characters.

Conclusion

Converting a DataTable to a CSV file in C# is a straightforward process. By following the steps outlined in this article, you can easily convert your tabular data to a CSV file format. Remember to handle special characters properly if your DataTable contains fields with commas or double quotes.