read text file by T-SQL

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

Where is the file? You can try with linked server or load it into sql table with bcp.

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.

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

Why can't load data in the file to sql table directly?

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

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.

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!

I am using SQL Server 2008 and Windows Server 2008. I only get the first character of the file?
Please help.
Thank you,
Lon

With above code?

the file needs to be ANSI format.
Thanks,
Lon

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.

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