none
import excel file into sql database RRS feed

  • Question

  • hello guys I'm having an issue with a simple project that I build, I'm a beginner and I'm trying to import excel file into sql database, I only can import one table into the database, and I need to import more than one tables into database, is anyone can help me with this problem ? I would really appreciate it and here's the code

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim koneksi_excel As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & TextBox1.Text & "';Extended Properties=""Excel 12.0 Xml;HDR=YES""")
            koneksi_excel.Open()

            Dim query_excel As String = "Select * from [Sheet1$]"

            Dim cmd As OleDbCommand = New OleDbCommand(query_excel, koneksi_excel)
            Dim rd As OleDbDataReader
            rd = cmd.ExecuteReader()

            Dim koneksi As New SqlConnection()
            Dim koneksidatabase As String = "server=DESKTOP-KJQ8PNO\SQLEXPRESS;DATABASE=otto;Integrated Security=True"
            koneksi.ConnectionString = koneksidatabase
            koneksi.Open()

            Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(koneksi)
                bulkcopy.DestinationTableName = "Table_1" 
                bulkcopy.BulkCopyTimeout = 600
                bulkcopy.WriteToServer(rd)
                rd.Close()
                koneksi.Close()

                MsgBox("Data uploaded to database", MsgBoxStyle.Information, "Uploaded")
                TextBox1.Text = ""

            End Using


        End Sub

    Wednesday, March 8, 2017 1:02 PM

All replies

  • Are you saying that you need to import all the Worksheets from an Excel Workbook into SQL Server? If not, which Worksheets do you need to import?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 8, 2017 2:56 PM