Import CSV file data into database using C# and VB.Net in ASP.Net

Hi makumbi,

As your record contains comma in Timestamp column you need to do some work to insert proper record.

Check this example. Now please take its reference and correct your code.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
<hr />
<asp:GridView runat="server" ID="GridView1"></asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Code

C#

protected void Upload(object sender, EventArgs e)
{
    string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(csvPath);
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[4] {
        new DataColumn("Full Name", typeof(string)),
        new DataColumn("User Action", typeof(string)),
        new DataColumn("Timestamp", typeof(string)),
        new DataColumn("Times", typeof(string)) });

    string csvData = File.ReadAllText(csvPath);
    bool firstRow = true;
    foreach (string row in csvData.Split(','))
    {
        if (firstRow)
            firstRow = false;
        else if (!string.IsNullOrEmpty(row))
        {
            dt.Rows.Add();
            int i = 0;
            foreach (string cell in row.Split(','))
            {
                dt.Rows[dt.Rows.Count - 1][i] = cell.Trim().Replace("\"", "").Replace("�", "");
                i += 1;
            }
        }
    }

    DataTable dtFinal = dt.Clone();
    dtFinal.Columns.Remove("Times");

    foreach (DataRow row in dt.Rows)
    {
        DataRow dr = dtFinal.NewRow();
        dr["Full Name"] = row["Full Name"];
        dr["User Action"] = row["User Action"];
        dr["Timestamp"] = row["Timestamp"] + ", " + row["Times"];
        dtFinal.Rows.Add(dr);
    }

    GridView1.DataSource = dtFinal;
    GridView1.DataBind();

    string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(consString))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            // Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.Msattendence";
            con.Open();
            sqlBulkCopy.WriteToServer(dt);
            con.Close();
        }
    }
}

VB.Net

Protected Sub Upload(sender As Object, e As EventArgs)
    Dim csvPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(csvPath)
    Dim dt As New DataTable()
    dt.Columns.AddRange(New DataColumn(3) {
                        New DataColumn("Full Name", GetType(String)),
                        New DataColumn("User Action", GetType(String)),
                        New DataColumn("Timestamp", GetType(String)),
                        New DataColumn("Times", GetType(String))})

    Dim csvData As String = File.ReadAllText(csvPath)
    Dim firstRow As Boolean = True
    For Each row As String In csvData.Split(ControlChars.Lf)
        If firstRow Then
            firstRow = False
        Else
            If Not String.IsNullOrEmpty(row) Then
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As String In row.Split(","c)
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Trim().Replace("""", "").Replace("�", "")
                    i += 1
                Next
            End If
        End If
    Next

    Dim dtFinal As DataTable = dt.Clone()
    dtFinal.Columns.Remove("Times")

    For Each row As DataRow In dt.Rows
        Dim dr As DataRow = dtFinal.NewRow()
        dr("Full Name") = row("Full Name")
        dr("User Action") = row("User Action")
        dr("Timestamp") = row("Timestamp") & ", " & row("Times")
        dtFinal.Rows.Add(dr)
    Next

    GridView1.DataSource = dtFinal
    GridView1.DataBind()

    Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(consString)
        Using sqlBulkCopy As New SqlBulkCopy(con)
            'Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.Msattendence"
            con.Open()
            sqlBulkCopy.WriteToServer(dt)
            con.Close()
        End Using
    End Using
End Sub

Screenshots

The CSV File

The Form

Database record after inserted