ASP.NET  

Importing/Reading/Exporting of CSV File in ASP.Net

.NET provides a very simple way to import, read and export .CSV files.

Now I will create an ASP.Net application where I will bind a grid with the imported .CSV file.

Then you can also export the displayed data in CSV file format.

Step 1

In Visual Studio 2013 first create a simple web application and add a web form just like “Default.aspx”.

default

Step 2

Add a folder like “upload” for storing the imported .CSV file.

Step 3

Prepare a .CSV file for import in the following format.

id

Step 4

Add the following controls to the Deafult.aspx file.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>Import/Export/Read CSV file</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.         <div>  
  12.             <div id="dvforgeneratingexcel">  
  13.                 <div>  
  14.                     <asp:GridView ID="wdgList" runat="server" Height="410px" AutoGenerateColumns="False"  
  15.                         Style="width: 99.8%;">  
  16.                         <Columns>  
  17.                             <asp:BoundField DataField="ID" HeaderText="ID"></asp:BoundField>  
  18.                             <asp:BoundField DataField="Name" HeaderText="Name"></asp:BoundField>  
  19.                         </Columns>  
  20.   
  21.                     </asp:GridView>  
  22.                 </div>  
  23.                 <div class="pull-right">  
  24.                     <asp:Button runat="server" ID="btnExport" Text="Export" OnClick="btnExport_OnClick" />  
  25.                    </div>  
  26.             </div>  
  27.             <div id="dvforexcelimport">  
  28.                 <asp:FileUpload Width="300" ID="FileUpload1" CssClass="form-control" runat="server" />  
  29.                 <asp:Button ID="btn_import" runat="server" CssClass="btn btn-default" Text="Upload Excel sheet" OnClick="btn_import_Click" />  
  30.             </div>  
  31.   
  32.         </div>  
  33.     </form>  
  34. </body>  
  35. </html>  
Step 5
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.OleDb;  
  5. using System.IO;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Web;  
  9. using System.Web.UI;  
  10. using System.Web.UI.WebControls;  
  11.   
  12. public partial class _Default : System.Web.UI.Page  
  13. {  
  14.     DataTable dt = new DataTable();  
  15.     DataTable CSVTable = new DataTable();  
  16.     protected void Page_Load(object sender, EventArgs e)  
  17.     {  
  18.     }  
  19.     /// <summary>  
  20.     /// Exporting of the .CSV file.  
  21.     /// </summary>  
  22.     /// <param name="sender"></param>  
  23.     /// <param name="e"></param>  
  24.     protected void btnExport_OnClick(object sender, EventArgs e)  
  25.     {  
  26.         try   
  27.         {   
  28.         Response.Clear();  
  29.         Response.ClearContent();  
  30.         StringBuilder sb = new StringBuilder();  
  31.         sb.AppendLine("ID,Name");  
  32.         Response.ContentType = "application/x-msexcel";  
  33.   
  34.         Response.AddHeader("content-disposition", "attachment; filename=ManageList.csv");  
  35.         Response.Write(sb.ToString());  
  36.         if (ViewState["CSVTable"] != "")   
  37.         {   
  38.         dt = ViewState["CSVTable"] as DataTable;  
  39.         if ((dt != null) && (dt.Rows.Count > 0))   
  40.         {  
  41.         foreach (DataRow row in dt.Rows)  
  42.         {  
  43.             sb = new StringBuilder((string)row[0]);  
  44.             for (int i = 1; i < dt.Columns.Count; i++)  
  45.             {  
  46.                 if (row[i] is DBNull)  
  47.                     sb.Append(",NULL");  
  48.                 else if (i == 2)  
  49.                     sb.Append("," + new DateTime((long)row[i]).ToString("G"));  
  50.                 else  
  51.                     sb.Append("," + row[i].ToString());  
  52.   
  53.             }  
  54.             sb.AppendLine();  
  55.             Response.Write(sb.ToString());  
  56.         }  
  57.         }  
  58.         }  
  59.         Response.Flush();  
  60.         Response.Close();  
  61.         Response.End();  
  62.         }  
  63.         catch (Exception ex) { }  
  64.   
  65.     }  
  66.     /// <summary>  
  67.     /// Importing of .CSV file.  
  68.     /// </summary>  
  69.     /// <param name="sender"></param>  
  70.     /// <param name="e"></param>  
  71.     protected void btn_import_Click(object sender, EventArgs e)  
  72.     {  
  73.         try  
  74.         {  
  75.             if (FileUpload1.HasFile)  
  76.             {  
  77.                 int flag = 0;  
  78.                 string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
  79.                 string RandomName = DateTime.Now.ToFileTime().ToString();  
  80.                 string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
  81.                 string FolderPath ="~/upload/";  
  82.   
  83.                 string FilePath = Server.MapPath(FolderPath + RandomName + FileName);  
  84.   
  85.                 string[] filenames = Directory.GetFiles(Server.MapPath("~/upload"));  
  86.   
  87.                 if (filenames.Length > 0)  
  88.                 { foreach (string filename in filenames)  
  89.                     {  
  90.                         if (FilePath == filename)  
  91.                         {  
  92.   
  93.                             flag = 1;  
  94.                             break;  
  95.                         }  
  96.                     }  
  97.   
  98.                     if (flag == 0)  
  99.                     {  
  100.                         FileUpload1.SaveAs(FilePath);  
  101.                         ReadCSVFile(FilePath);  
  102.                        }  
  103.   
  104.                 }  
  105.                 else  
  106.                 {  
  107.                  FileUpload1.SaveAs(FilePath);  
  108.                   ReadCSVFile(FilePath);  
  109.                   }  
  110.             }  
  111.             else  
  112.             {  
  113.                 String msg = "Select a file then try to import";  
  114.             }  
  115.         }  
  116.   
  117.         catch (Exception ex)  
  118.         {  
  119.             throw ex;  
  120.         }  
  121.     }  
  122.     /// <summary>  
  123.     /// Reading of the .CSV file.  
  124.     /// </summary>  
  125.     /// <param name="fileName"></param>  
  126.     public void ReadCSVFile(string fileName)  
  127.     {  
  128.         try {   
  129.         string connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='Text;HDR=Yes;FMT=CSVDelimited'";  
  130.   
  131.         connection = String.Format(connection, Path.GetDirectoryName(fileName));  
  132.   
  133.           
  134.         OleDbDataAdapter csvAdapter;  
  135.         csvAdapter = new OleDbDataAdapter("SELECT * FROM [" + Path.GetFileName(fileName) + "]", connection);  
  136.   
  137.         if (File.Exists(fileName) && new FileInfo(fileName).Length > 0)  
  138.         {  
  139.             try  
  140.             { csvAdapter.Fill(CSVTable);  
  141.                 if ((CSVTable != null) && (CSVTable.Rows.Count > 0))  
  142.                 {  
  143.                     ViewState["CSVTable"] = CSVTable;  
  144.                     wdgList.DataSource = CSVTable;  
  145.                     wdgList.DataBind();   
  146.                 }  
  147.                 else  
  148.                 {  
  149.                     String msg = "No records found";  
  150.                 }  
  151.             }  
  152.             catch (Exception ex)  
  153.             {  
  154.                 throw new Exception(String.Format("Error reading Table {0}.\n{1}", Path.GetFileName(fileName), ex.Message));  
  155.             }  
  156.         }  
  157.         }  
  158.         catch (Exception ex) { }  
  159.     }  
  160. }  
Thanks.

Membership not found