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

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Please help me with this small issue, I am using the following code: 

        Dim csvPath As String = Server.MapPath("/assets/public/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
        FileUpload1.SaveAs(csvPath)

        Dim dt As New DataTable()
        dt.Columns.AddRange(New DataColumn(9) {
                            New DataColumn("inv", GetType(String)),
                            New DataColumn("carrier", GetType(String)),
                            New DataColumn("service", GetType(String)),
                            New DataColumn("tracking", GetType(String)),
                            New DataColumn("name", GetType(String)),
                            New DataColumn("town", GetType(String)),
                            New DataColumn("zip", GetType(String)),
                            New DataColumn("charge", GetType(String)),
                            New DataColumn("status", GetType(String)),
                            New DataColumn("universal", GetType(String))})

        Dim csvData As String = File.ReadAllText(csvPath)
        For Each row As String In csvData.Split(ControlChars.Lf)
            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
                    i += 1
                Next
            End If
        Next

        Dim consString As String = ConfigurationManager.ConnectionStrings("00_ConnectionString").ConnectionString
        Using con As New SqlConnection(consString)
            Using sqlBulkCopy As New SqlBulkCopy(con)
                sqlBulkCopy.DestinationTableName = "dbo.manifest"
                con.Open()
                sqlBulkCopy.WriteToServer(dt)
                con.Close()
            End Using
        End Using

It works great, except, when I have a comma in one of my cells it says 

Cannot find column 10.

How can I replace the commas for a blank space? I do not need any commas in my sheet, here is what I mean:

"Address1, Address2, Town, Zip"

The original code was here: Bulk Import CSV file data into database using SqlBulkCopy in ASP.Net

Posted 5 months ago

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