asked on

Import Excel using VB 2005 into SQL by button click

I am working on a front-end for a SQL database. I'm using VB 2005. The SQL server is 2000. I need a solution for a user to click on a button to bring up a file open window. Then the user will navigate to the Excel file, which needs to be imported into SQL. The Excel file will have the same fields as the SQL database. It's just the file name will be different each time so they have to navigate to the correct file through the file open window. Once the user chooses the file and click on open. I need the Excel file to be imported into the table on the SQL server. Any help is appreciated very much.

Bob Learned

1) Use OleDb to read the Excel file into a DataTable

2) Use the SqlBulkCopy to insert the data into the SQL Server table.

Bob

hatforce

ASKER

I guess I need to work on the code to actually import the data first before moving onto creating a file open window for the users.

Ok. I have the following code to open a specific file on my C:\ drive:

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Product Add Spreadsheettest.xls;Extended Properties=""Excel 8.0;HDR=Yes""")
ExcelConnection.Open()
Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Data Source=A\DATASERVER;Initial Catalog=DATA1]; SELECT * FROM [DATA Add];", ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
End Sub

When I try to click the button I get an error "OleDbException was unhandled" Syntex error in INSERT INTO statement. The highlighted error references to "ExcelCommand.ExecuteNonQuery()".

My SQL server is local (for now) it will be remote when the app is launched for production.

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial