How to Convert a DataTable to CSV in C#

In this article, I am going to explain how to convert the datatable to a CSV file using c# with examples. I will show you an efficient and easy way to export data from the data table and generate a CSV file. 

Data is an essential component of every application. An application developer should know how to handle and manipulate data effectively. Data can be in different formats, such as XML, JSON, or CSV. So, in this article, we will discuss how to convert a datatable to a CSV file using C# with an example.

In my previous article, I explained how to convert images to text in c#, read large text file batch wise using c#, the efficient way to write CSV files from datatable, how to convert JSON to datatable or dataset using c# as well as how to perform CRUD operation using angular 14 with web API,  that you might like to read.

What is a DataTable?

A DataTable is a .NET class used to store data in a tabular format. It is similar to a database table, and it contains rows and columns. Each column in a DataTable has a specific data type, and each row represents a unique record. A DataTable is an in-memory representation of data and is often used to read and manipulate data from a database or a file.

What is CSV?

CSV stands for Comma Separated Values. It is a file format used to store data in a plain text format, where each value in a row is separated by a comma. CSV files are commonly used to exchange data between different applications.

Why Convert DataTable to CSV?

There are several reasons why we may need to convert a DataTable to a CSV file. Some of these reasons are:

1. Data Backup: It is often necessary to take a backup of the data stored in a DataTable. A CSV file is an ideal format for storing data backups as it is easy to read and can be opened using a text editor.
2. Data Exchange: CSV files are commonly used to exchange data between different applications. By converting a DataTable to a CSV file, we can easily share the data with other applications.
3. Data Analysis: CSV files are often used for data analysis purposes. By converting a DataTable to a CSV file, we can easily import the data into a data analysis tool such as Excel or Power BI.

Converting Datatable to CSV Using C# with Example

To convert a DataTable to a CSV file in C#, we can use the StringBuilder class. The StringBuilder class provides methods for appending strings, and it is efficient when we need to concatenate a large number of strings.

The following code demonstrates how to convert a DataTable to a CSV file using C#:

public static void ConvertDataTableToCsv(DataTable dataTable, string filePath)
        {
            StringBuilder sb = new StringBuilder();
 
            foreach (DataColumn column in dataTable.Columns)
            {
                sb.Append(column.ColumnName + ",");
            }
 
            sb.Remove(sb.Length - 1, 1);
            sb.Append(Environment.NewLine);
 
            foreach (DataRow row in dataTable.Rows)
            {
                foreach (DataColumn column in dataTable.Columns)
                {
                    sb.Append(row[column].ToString() + ",");
                }
 
                sb.Remove(sb.Length - 1, 1);
                sb.Append(Environment.NewLine);
            }
 
            File.WriteAllText(filePath, sb.ToString());
        }

Let's understand the code step-by-step:

1. We declare a static method named ConvertDataTableToCsv that takes two parameters, a DataTable, and a file path.
2. We declare a StringBuilder object named sb. We will use this object to build the CSV string.
3. We loop through each column in the DataTable and append the column name to the StringBuilder object, followed by a comma.
4. We remove the last comma from the StringBuilder object and add a new line character.
5. We loop through each row in the DataTable and append each value to the StringBuilder object, followed by a comma.
6. We remove the last comma from the StringBuilder object and add a new line character.
7. Finally, we write the contents of the StringBuilder object to a file using the File.WriteAllText method.

Using the Code to Convert DataTable to CSV

To use the code to convert a DataTable to a CSV file, we must create a DataTable object and populate it with data. We can then call the ConvertDataTableToCsv method, providing the DataTable and the file path as parameters. Here's an example:

In this example, we create a DataTable object and add three columns (CustomerId, CustomerName, ProductName, and Price) of different data types. We then add three rows of data to the DataTable.

// Create a DataTable object
DataTable dataTable = new DataTable();
 
// Add columns to the DataTable
dt.Columns.Add("CustomerId", typeof(int));
dt.Columns.Add("CustomerName", typeof(string));
dt.Columns.Add("ProductName", typeof(string));
dt.Columns.Add("Price", typeof(double));
 
// Add rows to the DataTable
dt.Rows.Add(1, "Nikunj Satasiya", "Laptop", 55000);
dt.Rows.Add(2, "Hiren Dobariya", "Mouse", 1022);
dt.Rows.Add(3, "Vivek Ghadiya", "Pen", 350);
dt.Rows.Add(4, "Dinesh Barad", "Laptop", 144000);
dt.Rows.Add(5, "Priya Patel", "Pendrive", 650);
dt.Rows.Add(6, "Kishan Kumar", "Charger Cable", 302);
 
// Convert the DataTable to a CSV file
ConvertDataTableToCsv(dataTable, "C:\\Users\\NikunjSatasiya\\Desktop\\Customers.csv");

Finally, we call the ConvertDataTableToCsv method, passing the DataTable object and the file path ("C:\Users\NikunjSatasiya\Desktop\Customers.csv") as parameters.

The output file (Customers.csv) will contain the following data:

This is a basic example of how to convert a DataTable to a CSV file using C#. You can modify the code to suit your needs, such as adding header and footer lines or changing the separator character.

Conclusion

In this article, we discussed how to convert a DataTable to a CSV file using C#. We saw that the StringBuilder class is a helpful tool for concatenating strings efficiently.

We also discussed why we may need to convert a DataTable to a CSV file, including data backup, data exchange, and data analysis.

By following the code example provided in this article, you can easily convert a DataTable to a CSV file in your C# application.