I also wrote an article on Exporting Dataset/Datatable to CSV file using c# and vb.net, recently, in the few months, I have received many requests from developers and beginners by email about publishing an article to export Datatable to CSV with a faster and efficient way using c# to compare to previously written article.So today in this article I will explain the same with different logic to export Datatable to CSV using LINQ using c#
demand
1) Create a method that returns a Datatable
2) Export data table to CSV file.
Application
So let’s start with the demo and Web form design With Gridview and one simple button, where we will present Datatable records to the user using Gridview. When the user clicks the button, all Datatable data/records will be exported to 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 table of data 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, a link is created GridView and assign the data table as a data source to the Gridview while the Loading pages.
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 button click event “Export to CSV”To export all Datatable records 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 a Datatable object dtCSV And move the Gridview datasource to Datatable and assign it to the datatable dtCSVNext we checked the condition for the data table dtCSV If you have a data table dtCSV empty or not and if it is not empty and has records then we have created an object for the StringBuilder class “sb” and retrieve the column name from Datatable and assign to the string array “the column names” and finally joined the array items the column names” with a comma appended to the StringBuilder object using the AppendLine. Finally, using a loop retrieve the records from the Datatable and using the LINQ selects Datarow records and stores the result in Local IE number variable called fields and enclose it with a comma and attached to the object of StringBuilder using 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 close the file.
Note: If the target file already exists then File.WriteAllText will replace the file.
Productivity
Summary
In this article, we learned an efficient way to export Datatable to CSV files using LINQ in C#.
Tags:
Data table to csv c# csvhelper
Export data table to csv file Download in c#
Write data table to csv c# streamwriter
dataset to csv c#
excel data table in c#
Data table to csv r
c# export sql table to csv
data table for c# memory stream
Data table to csv python
Data table to csv javascript
data table to csv file c#
Export data table to csv file Download in c#
Data table to csv uipath
Write data table to csv c# streamwriter
Data table to csv c# csvhelper
asp net datatable to csv file
