SQL Bulk Copy from Multiline TextBox/CSV?

I am trying to parse CSV data from a multiline textbox into a SQL table.  The process runs & inserts the proper number or rows but inserts nulls for each column.

webformtable
Using Con As New SqlConnection(ConfigurationManager.ConnectionStrings("GCP21DBConnectionString").ConnectionString)
            Con.Open()
            Using copy As New SqlBulkCopy(con)
                Dim dt As New DataTable()
                dt.Columns.AddRange(New DataColumn() {New DataColumn("FirstName", GetType(String)), New DataColumn("LastName", GetType(String)), New DataColumn("Email1", GetType(String)), New DataColumn("Email2", GetType(String)), New DataColumn("Institution", GetType(String)), New DataColumn("PPNum", GetType(Integer))})
                Dim txtList = DirectCast(DetailsView1.FindControl("txtList"), TextBox)
                Dim lines As String() = txtList.Text.Split(ControlChars.Lf)
                For Each row As String In lines
                    If Not String.IsNullOrEmpty(row) Then
                        Dim columns As String() = row.Split(","C)
                            For Each item In columns
                                dt.Columns.Add(New DataColumn())
                            Next item
                            dt.Rows.Add(row)
                    End If
                Next
                copy.ColumnMappings.Add(0, 0)
                copy.ColumnMappings.Add(1, 1)
                copy.ColumnMappings.Add(2, 2)
                copy.ColumnMappings.Add(3, 3)
                copy.ColumnMappings.Add(4, 4)
                copy.DestinationTableName = "PrePaid"
                copy.WriteToServer(dt)
            End Using
        End Using

Open in new window



TIA,
Andrew
AhelblingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AhelblingAuthor Commented:
hmmmm.  Tried this and got different result.

        Using Con As New SqlConnection(ConfigurationManager.ConnectionStrings("GCP21DBConnectionString").ConnectionString)
            Con.Open()
            Using copy As New SqlBulkCopy(con)
                Dim dt As New DataTable()
                dt.Columns.AddRange(New DataColumn() {New DataColumn("FirstName", GetType(String)), New DataColumn("LastName", GetType(String)), New DataColumn("Email1", GetType(String)), New DataColumn("Email2", GetType(String)), New DataColumn("Institution", GetType(String)), New DataColumn("PPNum", GetType(Integer))})
                Dim txtList = DirectCast(DetailsView1.FindControl("txtList"), TextBox)
                Dim lines As String() = txtList.Text.Split(ControlChars.Lf)
                For Each row As String In lines
                    If Not String.IsNullOrEmpty(row) Then
                        Dim columns As String() = row.Split(","C)
                            For Each item In columns
                                dt.Columns.Add(New DataColumn())
                            Next item
                            dt.Rows.Add(row)
                    End If
                Next
                copy.ColumnMappings.Add("FirstName", "FirstName")
                copy.ColumnMappings.Add("LastName", "LastName")
                copy.ColumnMappings.Add("Email1", "Email1")
                copy.ColumnMappings.Add("Email2", "Email2")
                copy.ColumnMappings.Add("Institution", "Institution")
                copy.ColumnMappings.Add("PPNum", "PPNum")
                copy.DestinationTableName = "PrePaid"
                copy.WriteToServer(dt)
            End Using
        End Using

Open in new window

data
Robberbaron (robr)Commented:
this line dt.Rows.Add(row) is adding the full piece of text to the data table.  
it is NOT splitting it into columns.
see https://msdn.microsoft.com/en-us/library/z16c79x4(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-4

 
                '---------option 1---------------
                For Each row As String In lines
                    If Not String.IsNullOrEmpty(row) Then
                        Dim columns As Object() = row.Split(","C)
                            dt.Rows.Add(columns)
                    End If
                Next
 
                '---------option 2--------------
               Dim workRow As DataRow 
                For Each row As String In lines
                    If Not String.IsNullOrEmpty(row) Then
                        workRow = dt.NewRow()
                        Dim columns As string() = row.Split(","C)
                         for i=0 to columns.length-1
                            workRow(i)=columns(i)
                          next i
                            dt.Rows.Add(columns)
                    End If
                Next

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AhelblingAuthor Commented:
option 1 ?

ss
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

AhelblingAuthor Commented:
option 2

Capture.JPG
AhelblingAuthor Commented:
This is what I placed in the multiline textbox:

FirstName,LastName,email1,email2,Institution,PPNum
FirstName,LastName,email1,email2,Institution,PPNum
Robberbaron (robr)Commented:
when the error occurs, what is the value of i & columns(i)  ?   one may be null as Option2 is almost exactly the same as the msdn example.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.