You may have a need to append some text to a file. Suppose you have an existing file C:\test\myfile.txt. You want to add the text hello world to the end of the file. After that you want to add another line of text how are you? to the end of the file. How do you do that? Open a command prompt. In Windows, you could right-click the Start button and select Run and type cmd and press the Enter key. Navigate to the directory that contains your file. Below is a session at the command prompt that illustrates that.

To increase the size of the text and the window, hold down the Ctrl key and push the wheel of the mouse (if you have a wheel) foreward.

The code listing below shows the use of the following commands. Note that >> appends to a text file.

• cd – change directory
• echo – send out the following
• >> – append
• type – send the contents of the specified file to the console

C:\>cd test

C:\test>echo hello world >> myfile.txt

C:\test>echo how are you? >> myfile.txt

C:\test>echo ^<?xml version="1.0"?^> >> myfile.txt

C:\test>type myfile.txt
hello world
how are you?
<?xml version="1.0"?>

C:\test>

The >> characters appends and does not overwrite. If you want to overwrite, you can use the > character, as discussed below. A use-case for wanting to append and not overwrite, is when you are recording a session at the command prompt and you want to create a log file. You don’t want to keep overwriting the previous command with >.

DOS Escape Characters

The only way to add the third line (the xml prolog) is to use the escape character ^. This looks like an up arrow. The less than and greater than characters are redirection commands for DOS. You can override that interpretation by escaping those characters. You can search on the Internet for DOS Escape Characters for more information or just go to Rob van der Woude’s page.

Redirecting with >

Do you want to save DOS output in a text file? For example, do you want a list of all files and folders in a directory (folder) to be saved in a text file so that you can then copy and paste that list into Excel for further analysis? Perhaps you have your own similar use case. Use the DOS redirect character > to do this.

C:\test> dir > dirinfo.txt 

List of Folders without Subfolders

How would you get a list of folders in a directory (folder)? If you want all of the subdirectories you would add the /s switch to the following. The following command at the command line in Windows creates a file called folders.txt with a list of the folder names.

dir /b /o:n /ad > folders.txt

dir is the directory command. An easy way to open a command prompt in a folder that is deep down a hierarchy is to use Windows Explorer to navigate to it, click in the Address Bar, type cmd, and press the Enter key.

The /b forces bare format which has no heading or summary information. The /o specifies the sort order and the n is alphabetic by name. The /a specifies that we want to display files/folders that have specified attributes and the d specifies that we want to display directories. The > says to send the output to the following. The folders.txt is the name of the file that the output is sent to. If the file doesn’t yet exist it will be created. If it already exists it will be overwritten with the new content.

SQL Server Use Case

Knowing a little bit about the command line comes in handy when you are working in stored procedures in SQL Server and you need to export data to files. In SQL Server you can use xp_cmdshell. From within SQL Server, you can spawn a command shell to the operating system. Therefore you can run operating system commands from within a SQL script in SQL Server. You can also run it from a stored procedure. For some information on how to run OS commands from SQL Server have a look at the post called SQL Server xp_cmdshell

Merging Files from within SQL Server

The conclusion is: only type works, not copy, when merging a text file with an xml file. Let me set the stage. Suppose you have an XML document that you have successfully exported into a file called temp.xml. However, the computer system that you are sending the file to requires a header line that identifies the file type. Suppose that is something like CODE52983. That data is stored in a second file called mydata.xml. From withing SQL Server you need to merge the two files. Use the DOS command type and the append >>. You want to put the xml data under the header line in the mydata.xml file. Here is some sample code.

DECLARE @MergeString VARCHAR(200);
SET @MergeString = 'type temp.xml >> mydata.xml';
EXEC xp_cmdshell @MergeString;