Bulk Import CSV file data contains Comma separated value in column into database using C# and VB.Net in ASP.Net

Hi davidevans,

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

In the below sample i have comma in the address column.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" Text="Upload" OnClick="Upload" runat="server" />
<br /><br />
<asp:GridView runat="server" ID="gvCustomers" />

Namespaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text.RegularExpressions

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[] { 
        new DataColumn("Id", typeof(int)),
        new DataColumn("Name", typeof(string)),
        new DataColumn("Address",typeof(string)) });

    string csvData = File.ReadAllText(csvPath);
    foreach (string row in csvData.Split('\n'))
    {
        if (!string.IsNullOrEmpty(row))
        {
            dt.Rows.Add();
            int i = 0;
            Regex regx = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
            foreach (string cell in regx.Split(row))
            {
                dt.Rows[dt.Rows.Count - 1][i] = cell.Replace("\"", "");
                i++;
            }
        }
    }

    gvCustomers.DataSource = dt;
    gvCustomers.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.Customers";
            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 DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {
                        New DataColumn("Id", GetType(Integer)),
                        New DataColumn("Name", GetType(String)),
                        New DataColumn("Address", GetType(String))})
    Dim csvData As String = File.ReadAllText(csvPath)
    For Each row As String In csvData.Split(vbLf)
        If Not String.IsNullOrEmpty(row) Then
            dt.Rows.Add()
            Dim i As Integer = 0
            Dim regx As Regex = New Regex(",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))")
            For Each cell As String In regx.Split(row)
                dt.Rows(dt.Rows.Count - 1)(i) = cell.Replace("""", "")
                i += 1
            Next
        End If
    Next
    gvCustomers.DataSource = dt
    gvCustomers.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.Customers"
            con.Open()
            sqlBulkCopy.WriteToServer(dt)
            con.Close()
        End Using
    End Using
End Sub

Screenshots

Sample CSV file

DataSet viasualizer

DataTable binded in GridView