SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


How to read a text file using xp_cmdshell

xp_cmdshell is an extended stored procedure is used to read contents of a text file using SQL commands by SQL developers and database administrators. Generally the requirement is to read the output of xp_cmdshell as a SQL result set or to set the contents of text file to a SQL variable.
xp_cmdshell extended stored procedure 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

SQL Server xp_cmdshell procedure

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

Of course before using xp_cmdshell system stored procedure, a SQL Server database administrator should enable xp_cmdshell permissions on related SQL Server database instance.

exec master.dbo.xp_cmdshell 'type c:\sample.txt'
Code

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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.