How to read a text file using xp_cmdshell
You sometimes need to read the contents of a text file using SQL commands.
You may want to read the results into a SQL result set or you may want to set the contents of the text file to a variable.
xp_cmdshell is an extended stored procedure that enables SQL developers and SQL administrators to run dos commands on the underlying operating system.
Let's assume that the name of the text file we want to read is sample.txt and it is located in the root folder of C drive.
Open the command prompt by running the "cmd" command prompt on the RUN dialog box. On the command prompt screen type the below command shown as in the picture.
type c:\sample.txt
When you run the type command by pressing ENTER, the contents of the sample.txt text file will be displayed on the console.
Now we will make our t-sql code to run this "type @filename" dos command successfully on the Query Analyzer by the help of the
extended stored procedure xp_cmdshell
exec master.dbo.xp_cmdshell 'type c:\sample.txt'
If you run the below command on a local SQL Server instance the xp_cmdshell will be able to list the contents of the text file in the results pane.
The read lines from the text file are returned as a sql result set.
Next step will be to return the read lines of the text file saved in a sql variable.
First we need to save the returned result set into a temporary table.
set nocount on
create table #file_contents (
line_number int identity,
line_contents nvarchar(4000)
)
declare @file_contents nvarchar(4000)
declare @new_line char(2)
set @new_line = char(13) + char(10)
insert #file_contents
exec master.dbo.xp_cmdshell 'type c:\sample.txt'
select @file_contents =
isnull(@file_contents, '') + @new_line +
isnull(line_contents, '')
from #file_contents
drop table #file_contents
select @file_contents
set nocount off
We used the @new_line character while forming the @file_contents variable.
If you do not use @new_line character all the contents of the text file will
be returned as a single line. New line character or the line feed character
enables the multi line structure of the original text file.
|