This article gives an explanation about converting Datatable to CSV in c# and explains the efficient way to write CSV files from Datatable as well as shows you how to read records from the Datatable using LINQ and write it into CSV files using c#.
In my previous article, I explained read CSV files in asp.net with examples c# and vb.net and export dataset/Datatable to CSV file using c# and vb.net and export JSON to CSV using jquery/javascript and bootstrap in asp.net and export all the excel sheets to a dataset in c# and vb.net as well as export JSON data to excel/CSV file using angular js with bootstrap many other articles on ASP.NET, C#, and VB.NET.
I have also written an article about exporting Dataset/Datatable to CSV file using c# and vb.net, recently in the few months, I got many requests from developers and beginners via email regarding posting an article for exporting Datatable to CSV with a faster and efficient way using c# to compare to the previously written paper. So, today in this article I'll explain the same with different logic for exporting Datatable to CSV with the help of LINQ using c#
Requirement
1) Create a method that returns a Datatable
2) Export Datatable to.CSV File.
Implementation
So, Let's start with a demonstration and design a web form with Gridview and one simple button, where we will display records of Datatable to the user with help of Gridview. When the user will click on the button, all the data/records of Datatable will export into a CSV file.
HTML
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="articles_2020_03_CS" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Datatable to CSV</title> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script> </head> <body> <form id="form1" runat="server"> <div class="container"> <div class="form-row"> <br /> <div class="form-group col-md-12"> <center><h2>Product Sales Details</h2></center> </div> </div> <div class="form-row"> <div class="form-group col-md-12"> <div class="panel panel-default "> <div class="panel-heading">Sales Grid</div> <div class="panel-body"> <asp:GridView ID="grdCustomer" runat="server" CssClass="table table-bordered active active" AutoGenerateColumns="false" EmptyDataText="No records has been found."> <Columns> <asp:BoundField DataField="CustomerId" HeaderText="CustomerId" ItemStyle-Width="15" /> <asp:BoundField DataField="CustomerName" HeaderText="CustomerName" ItemStyle-Width="300" /> <asp:BoundField DataField="ProductName" HeaderText="ProductName" ItemStyle-Width="100" /> <asp:BoundField DataField="Price" HeaderText="Price" ItemStyle-Width="50" /> </Columns> </asp:GridView> </div> </div> </div> <div class="form-group col-md-2"> <asp:Button ID="btnExportCSV" runat="server" CssClass="btn btn-success" Text="Export to CSV" OnClick="btnExportCSV_Click" /> </div> </div> </div> </form> </body> </html>
To, write a CSV file we need a Datatable, and here I will create a simple method that returns a Datatable using c# for demonstration.
public DataTable GetData() { DataTable dt = new DataTable(); try { dt.Columns.Add("CustomerId", typeof(int)); dt.Columns.Add("CustomerName", typeof(string)); dt.Columns.Add("ProductName", typeof(string)); dt.Columns.Add("Price", typeof(double)); dt.Rows.Add(1, "Nikunj Satasiya", "Laptop", 37000); dt.Rows.Add(2, "Hiren Dobariya", "Mouse", 820); dt.Rows.Add(3, "Vivek Ghadiya", "Pen", 250); dt.Rows.Add(4, "Pratik Pansuriya", "Laptop", 42000); dt.Rows.Add(5, "Sneha Patel", "Lip Bam", 130); dt.Rows.Add(6, "Jhon Smith", "Sigar", 150); return dt; } catch (Exception ex) { Console.WriteLine(ex.ToString()); return null; } finally { if (dt != null) { dt.Dispose(); dt = null; } } }
Now, we will bind created GridView and assign the Datatable as a Datasource to the Gridview while the page load.
protected void Page_Load(object sender, EventArgs e) { try { try { grdCustomer.DataSource = GetData(); grdCustomer.DataBind(); } catch (Exception) { throw; } } catch (Exception) { throw; } }
Now, we will write the following code in the click event of a button "Export To CSV", to export all the records of Datatable to a CSV file.
protected void btnExportCSV_Click(object sender, EventArgs e) { try { DataTable dtCSV = new DataTable(); // Cast datasource of gridview to datatable dtCSV = (DataTable)grdCustomer.DataSource; //checked for the datatable dtCSV not empty if (dtCSV != null && dtCSV.Rows.Count > 0) { // create object for the StringBuilder class StringBuilder sb = new StringBuilder(); // Get name of columns from datatable and assigned to the string array string[] columnNames = dtCSV.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray(); // Create comma sprated column name based on the items contains string array columnNames sb.AppendLine(string.Join(",", columnNames)); // Fatch rows from datatable and append values as comma saprated to the object of StringBuilder class foreach (DataRow row in dtCSV.Rows) { IEnumerable<string> fields = row.ItemArray.Select(field => string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\"")); sb.AppendLine(string.Join(",", fields)); } // save the file File.WriteAllText(@"D:\Codingvila.csv", sb.ToString()); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); throw; } }
Explanation
As you can see in the code above here we have created an object of Datatable dtCSV and cast the Datasource of Gridview to Datatable and assigned it to the Datatable dtCSV, Then we have checked the condition for the Datatable dtCSV whether Datatable dtCSV is empty or not and if it is not empty and has records then we have created an object for the StringBuilder class "sb" and fetch the name of columns from Datatable and assigned to the string array "columnNames" and finally join the items of array columnNames" with comma appended into an object of StringBuilder using the AppendLine. Finally, with the help of a loop fetch the records from the Datatable and using the LINQ selects records of the Datarow and store the result into a local IEnumerable variable called fields and join it with a comma and appended into an object of StringBuilder using the AppendLine.
Finally, with the help of File.WriteAllText creates a new file and writes a specified string containing objects of the StringBuilder class "sb" and then closes the file.
Note: If the target file already exists then File.WriteAllText will overwrite the file.
Output
Summary
In this article, we learned an efficient way to export Datatable to CSV files with the help of LINQ in C#.
Tags:
datatable to csv c# csvhelper
export datatable to csv file download in c#
write datatable to csv c# streamwriter
dataset to csv c#
datatable to excel in c#
data table to csv r
c# export sql table to csv
datatable to memorystream c#
datatable to csv python
datatable to csv javascript
datatable to csv file c#
export datatable to csv file download in c#
datatable to csv uipath
write datatable to csv c# streamwriter
datatable to csv c# csvhelper
asp net datatable to csv file
