Keyboard Ninja: Concatenate Multiple Text Files in Windows
You have a directory full of log files that you want to import into Excel or a database so you can do some processing on them… but there are hundreds of files… how do you make them into a single file?
Answer: Pull out your DOS hat, open a command prompt, and then use the “for” command.
The syntax works something like this:
for <variablename> in (<directorylisting>) do <command> <variablename>
So if you wanted to append all of the *.log files in a directory, you’d use the “type” command and then pipe it into a single file using the >> operator.
The difference between >> and > is that the former appends data to the end of the file, and the latter will completely replace the file, which would be pointless for what we want to do.
So here’s the command you’d run, assuming you are in the directory containing the log files.
for %f in (*.log) do type “%f” >> aggregate.txt
And yes, I actually just used this command for a project at work, which is why I’m writing up this article. =)
Random thought: What on earth would a DOS hat look like?
Daily Email Updates
You can get our how-to articles in your inbox each day for free. Just enter your name and email below:
I just tried this. After all the .log files in the directory are added to the aggregate file, the entire aggregate file is then added to itself (because it is now a new file in the directory). Is there a way to keep this from happening if you would want to keep the aggregate file in the same directory that you are working in?
Skeeter:
That did not happen for me…. but your best bet is to output to a file with a different extension, like aggregate.txt
That way it wouldn’t be included in the list of files in the directory.
I updated the article to reflect that as well….
You can also just use the DOS copy command:
copy /a *.log aggregate.txt
The /a option ensures that any ^Z’s at end-of-file in the source files will not be copied to the destination file.
Much more easier with the copy DOS command and the whole aggregate file is not added at the end (happened to me too with the for method).
Thanks for this. It was a lifesaver when I needed to pull together a whole bunch of sql scripts into one big script.
Life Saver!!!!
LIFESAVER almost …
The “for %f in (*.log) do type “%f” >> aggregate.txt” technique concatenated all files but then multiplied everything by 2. There was also some issue with the last line from Report A and the first line in Report B.
The second command, “copy /a *.log aggregate.txt” worked best.
THANKS!
Brilliant!!! Thankyou thankyou. I was going cross-eyed manually importing all these text files into access one at a time.
Thanks!!!! This is a complete lifesaver!! So simple and effective, I got just the right data out of bazillion log files I had to go through.
This helped me lots. Thank you! The one thing I would add is that you can also use this technique in a batch file if you just use %%f instead of %f
brillant! thank you so much!
Thanx a lot! This really works!
I want to combine many different csv files. One group all have the very same file structure. The other has different structures where the order of the fields is different as is the lengths of fields, types, etc.?
Is there a simple way to take a name (i.e.) “Smith, John J.” or “John J. Smith” and separate them into individual fields as well as to create a calculated field where again the file is a csv type?
Thanks Ninja, this is the best solution i could expect for this concatenation question!!
Hey! Awesome solution, thanks for this!!!!
Kudos from me as well, you took the heavy out of my heavy next week!
nice solution, but what if i want a separator, like a line break before concatenate the next file, what is the syntax going to be ?
Nice solution, but I can go one simpler. How about
“type * > aggregate.txt” ?
as many said before, a lifesaver
Is it possible to have the original filename to become a part of the aggregate file?
For the for %f in (*.log) do type “%f” >> aggregate.txt solution, how can it be made to search all sub directories. I tried For the for %f in (’dir *.log /s’) do type “%f” >> aggregate.txt
This was a life saver.
As someone mentioned, I had to concatenate SQL scripts. I had to keep doing it in case a bug with a script was found and fixed. With hundreds of scripts it’s a pain in the ass to do it manually.
Also mentioned by someone else, when concatenating two files without blank lines at the end of either, the first line of file 2 seems to be appended to the last line of file 1. For me this caused a problem, but it was fixed a hell of a lot faster than manually running all of these scripts.
What ends up happening is the comment of script 2 is appended to the GO on the last line of script 1. IE: GO /*
I’ve been looking around everywhere for a way to add a new line after each file concatenation, as well as doing subdirectories. IE: */*.sql doesn’t work.
Anyway, this little script is very useful, so long as your files have a blank line at the end. I’m assuming this can be attributed to Chris’ issue posted on September 6, 2007 1:20 pm.
I also had the problem, that my files had no new line at the end and I needed the files to be appended with one in between instead of writing the first line of the next file appended to the last line of the previous file..
What I did is simply write a new batch file stored in the same directory with this content:
@echo off
type %1
echo.
Save it with the name newline.bat and now run the for loop using the batch file instead of directly the type command:
for %f in (*.log) do newline “%f” >> output.txt
This worked perfectly for me…
Maik
Maik Wiege – can you help me with your bat file example? I put this is a batch file and it did not work:
for %f in (*.htm) do newline “%f” >> output.txt
@echo off
type %1
echo.
my source files are *.htm and the output needs to be something like all.txt
I need a line break or something as there are 25,000 file that each have about 7 rows of html and i eventualy need to get them into a spreadsheet to manipulate them.
Before seeing this post, I had been using DOS like this: copy *.htm all.txt
the issue with that is that when I open them in Excel there is one cell with information from all 25,000 files in it rather than 25k rows of html.
Brilliant.
Had a problem with a load of .txt files that were output of a data-logger and this suggestion concatentated them just perfec’
Thanks
Old_Robin…
This worked like a charm for me. However, you do NOT need to make the input and output be in two different file formats!
(for %f in (*.txt) do type “%f” >> output.tx_) & RENAME output.tx_ output.txt
Enjoy!
Thank God for DOS… and you
Thank You!!!
You made my day!
To get round the destination file ‘concat itself’ prob, I put all the .csv’s in one folder, and told dos to concat everything to one named .csv. DOS doesn’t concat the destination file as well, because it has an extension! :
copy /a output.csv
A DOS hat would be cool blue….
A DOS hat would read:
” I H 8 B A S H ”
and on the backside, legal-print-size lettering:
“Wish I were BASH”
:]
oh wait, I actually had a question: Does this work for binary files?
If yes, would this work for binary files split under osx/bash with the built-in split command?
I am trying to transfer a 6GB iso to a networkless winpc through a FAT fs external HD. 4GB limit… eugh. Thus, split.
for binary files:
copy /b file1+file2+file3+file4 outfile