none
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.

    Friday, May 04, 2007 3:31 PM

Answers

All replies

  • 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.

    Friday, May 04, 2007 5:45 PM
  • 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

    Friday, May 04, 2007 6:29 PM
  • 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)".

    Friday, December 12, 2008 8:47 PM
  •  
    Can you try the oether options listed here.

    http://support.microsoft.com/default.aspx/kb/321686
    Sankar Reddy | http://sankarreddy.spaces.live.com/
    Monday, December 15, 2008 9:25 PM
  •  

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

    • Proposed as answer by KPatel12 Friday, May 28, 2010 8:58 PM
    Friday, May 28, 2010 8:57 PM