Wednesday, March 7, 2012

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

No comments:

Post a Comment