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
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.
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.
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.