ADO.NET and Excel

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I need to read the data in an Excel spreadsheet and store it in a sql
server table. I want to do this using ado.net rather than a DTS package so
that I can do some error checking and make it user friendly.

How do I create a data reader or data table when my source is an excel
spreadsheet or csv file?

Jay
 
¤ I need to read the data in an Excel spreadsheet and store it in a sql
¤ server table. I want to do this using ado.net rather than a DTS package so
¤ that I can do some error checking and make it user friendly.
¤
¤ How do I create a data reader or data table when my source is an excel
¤ spreadsheet or csv file?

Not sure whether you are working with an existing SQL Server table or not, but the below example may
help:

Function ExportExcelToSQLServer() As Boolean

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=" & "E:\My
Documents\Book5.xls" & ";" & _
"Extended Properties=""Excel
8.0""")

ExcelConnection.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Orders] IN ''
[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM [Orders];",
ExcelConnection)

ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()

End Function


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top