How to access to excel,csv or other formatted file using OLE DB(SQL Native Client)?

Hi~.

First, is it possible to access(read/write) to the excel,csv or my-own-formatted file using OLE DB(SQL Native Client)?

If possible, how?

You can create linked server to the excel spreadsheet as shown in : http://msdn2.microsoft.com/en-us/library/ms190479.aspx

You can use OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

or OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.

Providing an example using VB6 / ADO using SQLNCLI

Sub main()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=SQLNCLI;Data Source=<datasourcename>;Integrated Security=SSPI;"

'Refer to table using linked server
rs.Open "select * from <linkedsrvname>...TestTable", cn

'OpenRowset
Set rs = cn.Execute("select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\TestBook.xls','select * from [TestTable]')")

'OpenQuery
Set rs = cn.Execute("select * from OpenQuery(ExcelShare,'select * from TestTable')")

While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields.Item(i)
Next i
rs.MoveNext
Wend

Set rs = Nothing
cn.Close

End Sub