# Miscellaneous > Structured Query Language (SQL) >  read text file by T-SQL

## tutor

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

----------


## rmiao

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

----------


## mrdnewman

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.

----------


## Tazz092

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

----------


## rmiao

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

----------


## Tazz092

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

----------


## shaggy

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.

----------


## PABluesMan

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!

----------


## lonuel

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

----------


## rmiao

With above code?

----------


## lonuel

the file needs to be ANSI format.
Thanks,
Lon

----------


## ygonzalezdel

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.

----------


## joeb3651

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

----------

