import data from excel file to sql database using vb.net

Question

0.00/5 (No votes)

See more:

Hi,

I'm trying to develop a windows application in which I want to have an option to copy the data from an excel file to the sql database table.

I copied a code from the internet but I'm getting some errors in it.

This is my code:

C++

Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=example.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        ExcelConnection.Open()

       <big>Dim expr As String = "SELECT * FROM [Sheet1$]" </big>  
      Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim SQLconn As New SqlConnection()
        Dim ConnString As String = "Data Source=ASPIRE5741G-PC;Initial Catalog=exceltosql;Integrated Security=True"
        SQLconn.ConnectionString = ConnString
        SQLconn.Open()


        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = "emp"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using


    End Sub

the error is in the line

C++

Dim expr As String = &quot;SELECT * FROM [Sheet1$]&quot;

the error message is:

C++

The Microsoft Jet database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

But the Excel sheet name is the same, and I tried almost all the options available in the google search results.

Please help.

I am not able to continue my project without this .

Posted 30-Nov-10 7:52am

jasminkp

Updated 30-Nov-10 22:22pm

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 1-Dec-10 4:22am

Edited for Grammar.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2010-11-30T11:30:00

Take a look at this part of code:

Data Source=example.xls

So i ask: where is the path to example.xls file?

Data Source=C:\Folder1\Folder2\example.xls

Which version of Excel do you have?

Try to optimize your code.
1. use Try...Catch ex... Finally... End Try block to catch errors
2. ExcelConnection is never closed

See warnings about using SQLBulkCopy:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx[^]
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server[^]

vivekse · Answer 2 · 2010-11-30T19:26:00

Hi jasminkp

You can use this function to get records form excel it is tested and working.

Public Shared Function GetRecordsFromExcelSheet(ByVal strFilePath As String) As DataTable
            
            Try

                Dim strConnectionString As String
                strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
                                                "Data source='" & strFilePath & "'; " & "Extended Properties=Excel 8.0;"

                Dim outputTable = Nothing
                Using conn As New OleDb.OleDbConnection(strConnectionString)
                    conn.Open()
                    Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    Dim sheet As String = schemaTable.Rows(0)("TABLE_NAME").ToString()
                    Dim cmd As New OleDb.OleDbCommand("SELECT * FROM [" & sheet & "]", conn)
                    cmd.CommandType = CommandType.Text
                    Dim output As New DataSet()
                    outputTable = New DataTable(sheet)
                    output.Tables.Add(outputTable)
                    Dim objOleDbDataAdapter As New OleDb.OleDbDataAdapter(cmd)
                    objOleDbDataAdapter.Fill(outputTable)
                End Using
                Return outputTable
            Catch ex As Exception
                Throw ex
            End Try
        End Function

jasminkp · Answer 3 · 2010-11-30T21:08:00

Thanks for your reply...

I don't have any name for the table in the Excel file. And the file is there in the project folder itself.

For the first time am working with a windows applicaton..

So if possible give me the complete code to copy the contents to the sql table also.

Pls help...........

import data from excel file to sql database using vb.net

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0