import excel file in VB.net

[qfchen]

Hi,

A piece of simple code to import excel file to a database, as shown below, I have problem when I tried to open the connection, the error message shows Excel driver is not correct. where shall I get the correct driver string? thanks.

Private Sub OpenExcelFile_F ileOk(ByVal sender As System.Object, ByVal e As System.Componen tModel.CancelEv entArgs) Handles OpenExcelFile.F ileOk
Name = OpenExcelFile.F ileName
Dim ConnectionStrin g As String = _
"Drive = {Microsoft Excel Driver (*.xls)};Driver ID=790;" & "Dbq=" & Name
Dim con As New Odbc.OdbcConnec tion(Connection String)
Dim cmdSelect As New Odbc.OdbcComman d("SELECT * FROM [Sheet1$]", con)
Dim adapter As New Odbc.OdbcDataAd apter(cmdSelect )
Dim ds As New DataSet

Try
con.Open() 'problem here, excel driver error
adapter.Fill(ds , "Sheet1")
Catch ex As Exception
MsgBox(ex.ToStr ing)
Finally
con.Close()
End Try
EmployeeDataGri dView.DataSourc e = ds.Tables("Shee t")
End Sub

[Curtis Rutland]

Have you set up a DSN for the connection?

[qfchen]

Do you mean, to import an excel file, must setup DSN?

My real problem is getting the correct excel driver. I think something wrong with following string.

Dim ConnectionStrin g As String = _
"Drive = {Microsoft Excel Driver (*.xls)};Driver ID=790;" & "Dbq=" & Name

[Curtis Rutland]

Well, for connection strings, I always go to ConnectionStrin gs.Com.

I don't know why you want to use ODBC though. For an excel sheet, I'd use OLE DB. But either way, here's the link to the page(s) with connection strings:

Excel
Excel 2007

[qfchen]

Thank you very much.

What difference between ODBC and OLE DB? any sample code in OLE DB?

However after using the ODBC connection, when call con.open, an exception happened. it indicate "[ODBC driver manager]data source name not found and no default driver specified", how to install ODBC driver?

Thanks.

[Curtis Rutland]

Thank you very much.

What difference between ODBC and OLE DB? any sample code in OLE DB?

However after using the ODBC connection, when call con.open, an exception happened. it indicate "[ODBC driver manager]data source name not found and no default driver specified", how to install ODBC driver?

Thanks.

"data source name not found" means that you haven't set up a DSN. OLE DB doesn't need DSNs. The connectionstrin gs.com link has the OleDb connection strings, and otherwise, all your objects should be the same. Just replace Odbc with OleDb:
(this is just a sample, read the connectionistri ngs.com page for more info on the connection string. Also, remember to add the "Imports System.Data.Ole Db" to the top of the code page:

Imports System.Data.OleDb
.
.
.
Dim ConnectionString As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Dim con As New OleDbConnection(ConnectionString)
Dim cmdSelect As New OleDbCommand("SELECT * FROM Sheet1$]", con)
Dim adapter As New OleDbDataAdapter(cmdSelect)
        'and everything else could stay the same

[Curtis Rutland]

For the record, you don't have to use SQL to read an excel file. If you want, you can use the Excel engine through COM Interop.

Here's a tutorial on creating an Excel document, but you should be able to figure out how to read one from that.

[qfchen]

Thank you. it's working. Save me a lot of time. I will remember you, a very special name in the forum. thanks again

[Curtis Rutland]

Cool, glad I could help.