ADO.NET and Excel

[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

 

[Mark Rae]

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

http://www.google.com/search?source...s=GGLD,GGLD:2004-51,GGLD:en&q="ADO.NET"+Excel

 

[Paul Clement]

¤ 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)