How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1467861&SiteID=1

|||

For openrowset function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

Later you can use the following command to access csv or text files;

SELECT * FROM

OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from test.csv');

Regards,

Salman Shehbaz.

|||

we can also enable 'Adhoc Distributed Queries' option using the following code;

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE with override

GO

How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

here's a similar post, you might want to try the query posted here

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$]')