Binaryworld - Reading Text Files Into Tables with line numbers. ... [ SQL Server -> T-SQL ]

Some times we need to read text file line by line or read a specific line using T-SQL. This article will explain a simple technique using xp_CmdShell stored proc.

Let's first create a sample file which we will import into SQL Server table.

test.txt

Hello this is a text file
My name is Mr. bean
This is line 3
and this is last line

Save this text file as c:\test.txt and then run the following code to import the text file into temp table

Click here to copy the following block

CREATE TABLE #myText
  (linenum INTEGER IDENTITY (1, 1) NOT NULL
   CONSTRAINT PK_myText PRIMARY KEY CLUSTERED,
   myLine VARCHAR(8000)
)
Go

INSERT INTO #myText
   EXEC master.dbo.xp_cmdshell 'type c:\test.txt'
Go

select * from #myText order by linenum

Output

linenum     myLine                          
----------- --------------------------------
1           Hello this is a text file
2           My name is Mr. bean
3           This is line 3
4           and this is last line

(4 row(s) affected)

Wow.. how simple is that. You can now query any line from this table and you can use regular T-SQL to perform ome compelx operations.