How to query an remote excel file using OPENROWSET

Hi,

can anyone help me out this problem.

i want to query an excel file which at remote location through openrowset.

Select * from openrowset(.... ?

thanks

Sunila, this will query an excel spreadsheet that is local or on a shared network drive:

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Home\dannyboy\WORK\opeid_PEPS_database\PEPS_Reference_Codes.xls', [ref_territory$])

Replace the database=\\server\direcorty\filename.xls to access a different server (say a remote server). In the above case, ref_territory is a tab name in the Excel spreadsheet. Note you need to add a $ at the end of the tab name.

You could also define a linked server to the excel spreadsheet and then query without using OPENROWSET...

Assume Linked Server name is SomeLinkedServer

Select * from SomeLinkedServer...ref_territory$ (note there are 3 periods between Linked Server name and tab name)

See Books Online concerning OPENROWSET and/or Linked Servers for more info.

|||

thanks a lot

i need more clarification for the same. if the excel file is kept in a remote mechine then i need to access the same (\\ipaddress\foldername\xlsfilename.xls). the same query will work if i give like this?

|||Sunila, give it a try. It should work assuming the security is in place to allow you to access the remote directory or the services (SQL Agent, etc) have proper security if you are running this from a job...|||

i got another solution..

like

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=Yes;IMEX=1;database=\\<computername>\filename.xls;',
'SELECT * FROM [Sheet1$]')