Sunday, 23 February 2014

How to Upload CSV file in C# ASP.NET GridView

Description:

   In this article, I am going to write C# ASP.NET code example to Read or Upload CSV file into ASP.NET GridView using ASP FileUpload Control to get file path and TextFieldParser class from Microsoft.VisualBasic to parse CSV File content.

Summary:


Upload CSV file in ASP.NET- HTML markup

   Here, we have used ASP.NET's FileUpload control to browse CSV file location and GridView control to show Imported CSV file data.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Upload CSV File in ASP.NET</title>
    <style type="text/css">
        .gvCSVData
        {
            font-family: Verdana;
            font-size: 10pt;
            font-weight: normal;
            color: black;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h1>
            Upload CSV File in ASP.NET</h1>
        <table>
            <tr>
                <td>
                    <asp:FileUpload ID="FileUploadControl" runat="server" />
                    <asp:Button runat="server" ID="btCSVUpload" Text="Upload CSV" 
        OnClick="btCSVUpload_Click" />
                    <asp:Label runat="server" ID="lbStatus" 
Text="CSV Upload status: Select file" />
                </td>
            </tr>
            <tr>
                <td>
                    <asp:GridView ID="gvCSVData" CssClass="gvCSVData" runat="server" 
AutoGenerateColumns="True" HeaderStyle-BackColor="#61A6F8"
  HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White">
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Read CSV file in C# ASP.NET using TextFieldParser

   We can read data from CSV file in many of custom ways. In this article, we are going to read or import data from CSV file into .NET DataTable by using TextFieldParser. don't try to search this class in C# library because which is not available in C#. TextFieldParser is the Visual basic class. So we need to add reference dll Microsoft.VisualBasic.

  • Right-click the Reference, click Add Reference,select Microsoft.VisualBasic, and click OK button
Import CSV file in ASP.NET
private static DataTable GetDataTableFromCSVFile(string csvfilePath)
{
    DataTable csvData = new DataTable();
    using (TextFieldParser csvReader = new TextFieldParser(csvfilePath))
    {
        csvReader.SetDelimiters(new string[] { "," });
        csvReader.HasFieldsEnclosedInQuotes = true;

        //Read columns from CSV file, remove this line if columns not exits  
        string[] colFields = csvReader.ReadFields();

        foreach (string column in colFields)
        {
            DataColumn datecolumn = new DataColumn(column);
            datecolumn.AllowDBNull = true;
            csvData.Columns.Add(datecolumn);
        }

        while (!csvReader.EndOfData)
        {
            string[] fieldData = csvReader.ReadFields();
            //Making empty value as null
            for (int i = 0; i < fieldData.Length; i++)
            {
                if (fieldData[i] == "")
                {
                    fieldData[i] = null;
                }
            }
            csvData.Rows.Add(fieldData);
        }
    }
    return csvData;
}

Upload CSV file into ASP.NET GridView- CodeBehind

   This is the C# ASP.NET CodeBehind to read or import CSV file into ASP.NET GridView. The ASP FileUpload control will give only file name and HttpRequest object. So, we need to read data from HttpRequest object and save CSV file locally, then after uploaded the CSV file content in GridView, we can delete that file.

Note: Do not forgot to include the Reference Microsoft.VisualBasic to use TextFieldParser class.
using System;
using System.Data;
using System.IO;
using Microsoft.VisualBasic.FileIO;

namespace MorganWebProject
{
    public partial class UploadCSV : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btCSVUpload_Click(object sender, EventArgs e)
        {
            if (FileUploadControl.HasFile)
            {
                try
                {
                    string currentPath = Server.MapPath("~/") + 
                                  Path.GetFileName(FileUploadControl.FileName);
                    FileUploadControl.SaveAs(currentPath);

                    gvCSVData.DataSource = GetDataTableFromCSVFile(currentPath);
                    gvCSVData.DataBind();
                    lbStatus.Text = "CSV Upload status: File uploaded!";

                    File.Delete(currentPath);
                }
                catch (Exception ex)
                {
                    lbStatus.Text = @"CSV Upload status: The file could not be uploaded. 
                    The following error has occured: " + ex.Message;
                }
            }
            else
            {
                lbStatus.Text = "CSV Upload status: File not found.";
            }
        }

        private static DataTable GetDataTableFromCSVFile(string csvfilePath)
        {
            DataTable csvData = new DataTable();
            using (TextFieldParser csvReader = new TextFieldParser(csvfilePath))
            {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;

                //Read columns from CSV file, remove this line if columns not exits  
                string[] colFields = csvReader.ReadFields();

                foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }

                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
            return csvData;
        }
    }
}

Note: Here, we have assumed the CSV File's first line is column header, so that we are reading columns  by this line:
                string[] colFields = csvReader.ReadFields();
If your CSV file don't have the column header, you can just remove this line.

Output:

Read or Import CSV file in ASP.NET

Advertisements
Advertisements

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Greeting...!!
    I am a student and am new to .net(C#) Could you please till me why
    While using the above code , its throwing me error as
    "The following error has occured: Input array is longer than the number of columns in this table. "

    ReplyDelete
    Replies
    1. Hi Ruhi , it seems your CSV file contains incorrect column header .. can you post first three line of CSV file

      Delete
  3. Hi morgan,How can i read the binded Gridview row so that i can check some column and will be able to Insert into database?

    ReplyDelete