Showing posts with label formatted. Show all posts
Showing posts with label formatted. Show all posts

Sunday, February 19, 2012

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