Post reply

Read Text File

  • November 23, 2011 at 9:17 am

    #248540

    I am working in an SQL Server 2008 R2 environment and I wish to add a routine to my T-SQL script that reads a .txt file. I tried using the following routine but it fails. I would appreciate any help to resolve this challenge.

    select *

    from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};

    DefaultDir=W:\RetailSales;','select * from TARG01_styles.txt')

    -------

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

    Msg 7303, Level 16, State 1, Line 1

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

  • November 23, 2011 at 9:46 am

    #1412322

    It’s been a while since I opened a text file using T-SQL, but when I did it, I used the bulk provider and was very easy to do it. Did you try using the BULK provider? You can read about it in BOL (Books on line). Here is a small example:

    SELECT convert(varchar(max), BulkColumn) FROM OPENROWSET( BULK 'c:\MyFile.txt', SINGLE_BLOB) as MyTextFile

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 23, 2011 at 9:54 am

    #1412327

    Depending on what you need you could look at the SQLCLR at:

    http://nclsqlclrfile.codeplex.com/[/url]

    CEWII

  • November 23, 2011 at 10:48 am

    #1412363

    Thanks Adi. This works when the text file resides on the server. However, when the file is on a network server it fails. I suspect an SQL service account does not have rights to network folders. How do I give sufficient rights to this account?

    SELECT convert(varchar(max), BulkColumn) FROM OPENROWSET( BULK '\\EVY11\W-Drive\RetailSales\TARG01_styles.txt', SINGLE_BLOB) as MyTextFile

    ------

    Msg 4861, Level 16, State 1, Line 1

    Cannot bulk load because the file "\\EVY11\W-Drive\RetailSales\TARG01_styles.txt" could not be opened. Operating system error code 5(Access is denied.).

  • May 11, 2012 at 5:09 am

    #1486196

    there is an issue with the 64 bit installation of windows - it doesn't install the MSDASQL drivers

    MVDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply