Windows Command Prompt

By Becky Bell, Institutional Data Coordinator/Research Analyst, Office of Institutional Research, University System of Maryland

If you work with raw data files, there may be a time when you want to quickly combine the same type of files from either multiple years and/or from multiple sources. Windows Command Prompt, which allows users to write commands and perform tasks on the computer outside of the Windows interface, provides a quick and easy way to combine or merge multiple raw data files (.txt or .csv) that have the exact same file layout in fixed width or delimited format. It can also help you avoid spending time writing out the syntax or going through importing and merging the files one by one, especially if you do not expect to repeat the process or combine similar files again in the future.

The files to be merged MUST all have the EXACT same:

  • File layout (order of fields)
  • File format (can be all .txt or all .csv)

Also, the last record in each file must end with a Return to the next line (also known as a line feed/carriage return).

In this example, I start with four years of data collected and stored in four separate files. Each file includes the following fields: Term, Year, Student Level, GPA, and Last Name. To create a single merged file, follow the steps below.

oct2015tt1.JPG
1. Using Windows Explorer, place the raw data files you want to merge together in an empty folder.

     (Note: This process will merge ALL files of the same format found in that folder.)

2. Click in a blank area in the folder in which you have your files, so that no file is selected.

3. Hold Control + Shift while RIGHT clicking in a blank area to see a context pop-up menu.

4. In the menu, select “Open command window here” (located toward the bottom) to open the Command Prompt window.

oct2015tt2.JPG
5. Confirm the directory folder listed in the Command Prompt window is where your files are located. If not, go back to Step 2 and try again.

  • To merge .txt files, type the following red text (and replace MyNewMergedFile with the file name you want):
    • copy /B *.txt MyNewMergedFile.txt
  • To merge .csv files, replace the .txt with .csv in the above command:To merge .csv files, replace the .txt with .csv in the above command: 
    • copy /B *.csv MyNewMergedFile.csv

6. Press Enter to combine files.

oct2015tt3.JPG
7. The new merged file is created in the same folder as the other files, and looks like this:

oct2015tt4.JPG
oct2015tt5.JPG
Note: If your files had headers, you can open the merged file in a program like Excel or SPSS, then sort and delete the additional header records.

See the example data files with headers, and the merged file below:

oct2015tt6.JPG
oct2015tt7.JPG

 

 Comments

 
To add a comment, Sign In
Total Comments: 3
 
Gary posted on 10/14/2015 1:10 PM
Thanks for the interesting tip! The command line is a ubiquitous and powerful feature of all operating systems. I usually prefer my other data tools for these kinds of tasks, but I appreciate your shining a light into this dark corner of our toolboxes. I'd enjoy hearing from other IR professionals how they use the command line in their work as well.
Iroshan posted on 10/16/2015 3:54 PM
I never knew this could be done, I've always resorted to the cat command in bash
Lee posted on 10/20/2015 3:46 PM
Thanks Becky for a helpful tip! I haven't used the command line in a while, so it's good to have a refresher on its capabilities.