Solved

export csv datatable to sql

Posted on 2014-02-26
5
494 Views
Last Modified: 2014-02-26
can I have the syntax to export my datable to sql from what I have below.

Private Shared Function GetDataTabletFromCSVFile(csv_file_path As String) As DataTable
            Dim csvData As New DataTable()
            '  Try
            Using csvReader As New TextFieldParser(csv_file_path)
                csvReader.SetDelimiters(New String() {","})
                csvReader.HasFieldsEnclosedInQuotes = True
                Dim colFields As String() = csvReader.ReadFields()
                For Each column As String In colFields
                    Dim datecolumn As New DataColumn(column)
                    datecolumn.AllowDBNull = True
                    csvData.Columns.Add(datecolumn)
                Next
                While Not csvReader.EndOfData
                    Dim fieldData As String() = csvReader.ReadFields()
                    'Making empty value as null
                    For i As Integer = 0 To fieldData.Length - 1
                        If fieldData(i) = "" Then
                            fieldData(i) = Nothing
                        End If
                    Next
                    csvData.Rows.Add(fieldData)
                End While
            End Using
            '  Catch ex As Exception
            '  End Try
            Return csvData
        End Function

Open in new window

0
Comment
Question by:sevensnake77
  • 3
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
JB4375 earned 500 total points
ID: 39890239
The Basics:

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(yoursqlconnection)

If yourdatatable.Rows.Count > 0 Then

bulkCopy.DestinationTableName = "yoursqltable"

bulkCopy.BulkCopyTimeout =  (timeoutvalue)

bulkCopy.BatchSize = 10000

bulkCopy.WriteToServer(csvData)

End If

More info about the Bulk Copy Process here:
http://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39890290
You think that will work out with my code below.

 GetDataTabletFromCSVFile(Server.MapPath("~/csvfile"+ ".CSV"))


        'Protected GridView1 As Global.System.Web.UI.WebControls.GridView

        Private Shared Function GetDataTabletFromCSVFile(csv_file_path As String) As DataTable
            Dim csvData As New DataTable()
            '  Try
            Using csvReader As New TextFieldParser(csv_file_path)
                csvReader.SetDelimiters(New String() {","})
                csvReader.HasFieldsEnclosedInQuotes = True
                Dim colFields As String() = csvReader.ReadFields()
                For Each column As String In colFields
                    Dim datecolumn As New DataColumn(column)
                    datecolumn.AllowDBNull = True
                    csvData.Columns.Add(datecolumn)
                Next
                While Not csvReader.EndOfData
                    Dim fieldData As String() = csvReader.ReadFields()
                    'Making empty value as null
                    For i As Integer = 0 To fieldData.Length - 1
                        If fieldData(i) = "" Then
                            fieldData(i) = Nothing
                        End If
                    Next
                    csvData.Rows.Add(fieldData)

                    For Each TextFileTableDataRow As DataRow In csvData.Rows
                        Dim Column0 As String = IsDBNull(TextFileTableDataRow("column0"))
                        Dim Column1 As String = IsDBNull(TextFileTableDataRow("column1"))
                        Dim Column2 As String = IsDBNull(TextFileTableDataRow("column2"))
                        Dim Column3 As String = IsDBNull(TextFileTableDataRow("column3"))
                        Dim Column4 As String = IsDBNull(TextFileTableDataRow("column4"))
                        Dim Column5 As String = IsDBNull(TextFileTableDataRow("column5"))
                        Dim Column6 As String = IsDBNull(TextFileTableDataRow("column6"))

                        Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(HttpContext.Current.Application.Get("connection"))


                        Using command As New SqlCommand("INSERT INTO dbo.Table ([column0], [column1], [column2], [column3], [column4], [column5], [column6]) VALUES (@column0, @column1, @column2, @column3, @column4, @column5, @column6)", myConnection)
                            command.Parameters.AddWithValue("@column0", Column0)
                            command.Parameters.AddWithValue("@column1", Column1)
                            command.Parameters.AddWithValue("@column2", Column2)
                            command.Parameters.AddWithValue("@column3", Column3)
                            command.Parameters.AddWithValue("@column4", Column4)
                            command.Parameters.AddWithValue("@column5", Column5)
                            command.Parameters.AddWithValue("@column6", Column6)
                            '  command.Parameters.AddWithValue("@Column8", Column7)

                            myConnection.Open()
                            command.ExecuteNonQuery()

                        End Using
                    Next
                End While

            End Using
            '  Catch ex As Exception
            '  End Try
            Return csvData
        End Function

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39890460
Ok I am using it but I am getting
The ConnectionString property has not been initialized.

  Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(HttpContext.Current.Application.Get("connection"))

Open in new window


  Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(myConnection)
                        myConnection.Open()
                        If csvData.Rows.Count > 0 Then
                            bulkCopy.DestinationTableName = "dbo.table"
                            '   bulkCopy.BulkCopyTimeout = ("")
                            bulkCopy.BatchSize = 10000
                            bulkCopy.WriteToServer(csvData)

                        End If

                    End Using
                    myConnection.Close()

Open in new window

0
 
LVL 1

Expert Comment

by:JB4375
ID: 39890898
Sorry for the delay..... Basically it's telling you that it's looking for the connection string to the SQL table that you're going to send your data to.

Here's a sample code of where I was inserting into SQL table.  Your connection statement is the same, I just always preferred more of a breakdown so I could see what was going on.

Notice the connection string is "MyConnection" and then I define that connection within the web.config along with the credentials to connect with it.

(VB.NET)
Dim sConn As Data.SqlClient.SqlConnection
Dim sComm As Data.SqlClient.SqlCommand
Dim sAdapt As Data.SqlClient.SqlDataAdapter

sConn = New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString)

sComm = New Data.SqlClient.SqlCommand("INSERT INTO YourTable(Fieldname1, Fieldname2, Fieldname3) Values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')", sConn)

sAdapt = New Data.SqlClient.SqlDataAdapter(sComm)

(Web.Config File - Defines "My Connection")
<connectionStrings>
<clear/>
<add name="MyConnection" connectionString="server=YourSrvr;database=YourDB;UID=UserID;PWD=Pass" providerName="System.Data.SqlClient"/>
</connectionStrings>
0
 
LVL 9

Author Closing Comment

by:sevensnake77
ID: 39890928
thanks with some modifications.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now