Results 1 to 13 of 13

Thread: read text file by T-SQL

  1. #1
    Join Date
    Feb 2008
    Posts
    1

    read text file by T-SQL

    hi
    how can i read a txt file by using T-SQL commands.
    thanx

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Where is the file? You can try with linked server or load it into sql table with bcp.

  3. #3
    Join Date
    Mar 2008
    Posts
    1

    T-sql

    Could someone please assist me with a problem. I need to read a plain text file via T-SQL code or script. I have 100 users accounts that need to be locked and I want the SQL code to parse the file/read the ID, pass it into the code as an argument and lock it.

  4. #4
    Join Date
    Mar 2008
    Posts
    2
    The easiest way I can think of is to import the text file into Access database and create a table there.
    Then you have two options at this stage, either you can export the table from Access to SQL server or use a linked server to query the access table directly. Both of these should not take more few mins to do.
    To import the data into the access table, go to:
    File -> Get external Data -> import.
    And then choose text file and follow the steps.

    To export the data from access to sql server:
    Right click on the Access table. ->
    Click on export. ->
    Choose ODBC from the save file as type ->
    And follow the steps.

    I Hope this helps….


    Dr T

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Why can't load data in the file to sql table directly?

  6. #6
    Join Date
    Mar 2008
    Posts
    2
    You can create OLE DB connection to the text file using sp_addlinkedserver procedure. This in other words means creating a linked server to the text file.
    Example script:
    exec sp_addlinkedserver NW_TEXT, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0' 'C:\TEXTFILES', NULL, 'Text'

    Dr T

  7. #7
    Join Date
    Mar 2008
    Posts
    2
    You can also use "OPENROWSET" command. below show you a example

    select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
    DefaultDir=C:\;','select * from
    RNC.TXT')

    Greetings,

    Santiago Carela
    Rep. Dom.

  8. #8
    Join Date
    Mar 2008
    Posts
    11

    RE: read text file by T-SQL

    Okay, so there is a way to do this directly from T-SQL, but it's kinda involved. The sample code is below:

    CREATE PROCEDURE ReadFromTextFile

    @FileName VARCHAR (1024)

    AS
    DECLARE @OLEResult INT
    DECLARE @FS INT
    DECLARE @FileID INT
    DECLARE @Message VARCHAR (8000)

    -- Create an instance of the file system object
    EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
    IF @OLEResult <> 0
    BEGIN
    PRINT 'Scripting.FileSystemObject'
    PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
    END

    -- Open the text file for reading
    EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
    IF @OLEResult <> 0
    BEGIN
    PRINT 'OpenTextFile'
    PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
    END

    -- Read the first line into the @Message variable
    EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

    -- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
    WHILE @OLEResult >= 0
    BEGIN
    PRINT @Message

    EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
    END

    EXECUTE @OLEResult = sp_OADestroy @FileID
    EXECUTE @OLEResult = sp_OADestroy @FS

    Hope this helps!

  9. #9
    Join Date
    Jan 2009
    Posts
    2
    I am using SQL Server 2008 and Windows Server 2008. I only get the first character of the file?
    Please help.
    Thank you,
    Lon

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    With above code?

  11. #11
    Join Date
    Jan 2009
    Posts
    2

    I only get one character, but I believe the problem is

    the file needs to be ANSI format.
    Thanks,
    Lon

  12. #12
    Join Date
    Nov 2009
    Posts
    1

    Read a text file from t-sql

    Guys this is the way of doing it
    exec sp_OACreate 'Scripting.FileSystemObject', @oFile out
    exec sp_OAMethod @oFile, 'OpenTextFile', @objFile out, @File, 1
    exec sp_OAMethod @objFile, 'AtEndOfStream', @eof out

    you don't have to loop using the ReadLine method.
    Hope this helps.

  13. #13
    Join Date
    Dec 2009
    Posts
    1

    Giant text field

    Couple of questions here:

    exec sp_OACreate 'Scripting.FileSystemObject', @oFile out
    exec sp_OAMethod @oFile, 'OpenTextFile', @objFile out, @File, 1
    exec sp_OAMethod @objFile, 'AtEndOfStream', @eof out

    I'm new to this type of loading so excuse stupid questions. What are the variable types in these three lines and how big can they get?
    I have been using bulk import because the file I need to process comes with varying row delimiters (no, trying to get the data supplier to change to a standard just isn't going to happen). They got a new sys admin who now says bulk import is not allowed so bulk import access has been shut off. Ultimately what I would like to do is read characters until I hit a character which I'm getting from a parameter passed into the stored proc. That in effect would be a 'line' which I put in a single column in a table. Now here's the rub, our clients are using a mix of 2005 and 2008. I think a Filestream object column might work for this but not everyone uses 2008. And these files can be anywhere from 1K to hundreds of M. Any ideas?

    Thanks,

    joeb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •