Click here to Skip to main content
15,846,727 members
Please Sign up or sign in to vote.
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
Updated 30-Nov-10 23:22pm
v2
Comments
Dalek Dave 1-Dec-10 4:22am    
Edited for Grammar.

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[^]
 
Share this answer
 
v2
Comments
jasminkp 1-Dec-10 2:59am    
the file is in the project folder itself.
jasminkp 1-Dec-10 3:11am    
i also made a trial with the openfiledialog and selected the file from the saved location. But still its not working...........
Dalek Dave 1-Dec-10 4:22am    
Good Call.
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
 
Share this answer
 
v2
Comments
Dalek Dave 1-Dec-10 4:23am    
Edited for Code Blocks.
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...........
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900