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

Question

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.

Answer 1

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

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

Answer 2

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.

Answer 3

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

Answer 4

Advice would be appreciated on this one.

I have a folder called E:\Compare\NAV
I have a file in the folder called NAV.csv
I have Surface Area Configuration checked ON for OpenRowSet

This command fails:

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=E:\Compare\NAV;Extensions=CSV;','SELECT * FROM NAV.csv')

The error is:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Answer 5

 
Can you try the oether options listed here.

http://support.microsoft.com/default.aspx/kb/321686

---

Sankar Reddy | http://sankarreddy.spaces.live.com/

Answer 6

 

You need to add one more \ after NAV in ur command as I shown below. now try again with this

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=E:\Compare\NAV\;Extensions=CSV;','SELECT * FROM NAV.csv')