How-To Geek
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?
Got Feedback? Join the discussion at discuss.howtogeek.com
Comments (59)
Programmer by day, geek by night, The Geek, also known as Lowell Heddings, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.
- Published 07/2/07
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
Thanks a lot !!!
How do i put the filename of each individual file as a title line in the concatenated file?
Thanks a lot !!!
This is a lifesaver as everyone else has stated.
Thank you all.
Thanks for this article. I used these commands and modified them to work for all files in current dir AND all subdirectories. I also created a bat file named mashit.bat so I can copy and run it when I need it. So create one yourself and copy this code snippet into it.
for /f “usebackq” %%f in (`dir /b /s *.js`) do type “%%f” >> mashed.js_
rename mashed.js_ mashed.js
As you can see it runs through all the js files it finds and puts it into mashed.js_ . It ends with renaming the .js_ file to js. You can edit the js parts with what you need.
I work with EXTJS javascript framework and created a lot of js files in different subdirectories. These js files are reusable classes. For live production servers I concat (with mashit.bat) and minify (with YUI compressor) these javascript classes.
Sidenote I forgot to mention. If you use windows command prompt to run those commands only use 1 % and not %% – The mashit.bat file needs %% to indicate variables.
Awesome!
This tip was very helpful. One caveat: you should re-type the command by hand. The command in the HTML document above has ‘smart quotes’, which look nice in a HTML page, but confuse the command prompt. This caught me up when I tried putting it into a batch file.
Excellent
Thanks 4 sharing
I found your commands “for %f in (*.log) do type “%f” >> aggregate.txt” extremely useful, but after each log file, I wanted a blank page. Could you please tell me how to do this with the command.
I mean a txt file with all the log files aggregated, but with a blank page between each log file.
I created this file and it worked great! However, when I transferred this text file from computer I was using (windowsNT) to a different computer running (windowsXP professional) the txt file would not open. It said that the file was corrupt. Do you have any suggestions on how open the file? It doesn’t make sense as the file can still be opened on the original computer.
Thanks a million ! The command worked for me when I removed the double quotes. With quotes it was giving “File not found error”
i don’t know why I like this so much – buts it’s frickin awesome
Wow, don’t know DOS can do that. Great work dude.
I wanted to combine 19 files, and it did the task really well.
But I wanted to ask something, when i joined the files it started the next file where the last line ended.
I wanted to start the other file from next line, not at the ending line of previous file.
Like some of the people above I needed to put a large number of Stored Procedures together to send off to be executed. I also wanted to add some additional information to the file as well as Grant Execute for the proc to a specific group. I also have to do this for Different Databases, which are in different directories so I made it so that I passed the needed parameters to the bat files so that I could keep these bat files in one location and do the work anywhere and also put the output file where I needed it, which isn’t the directory where the .SQL files are located.
So I created a couple of bat files. First one is the one you call directly passing it in all the information it needs to run and I named it CombineStoredProcedures.bat
cd %2
del %3
(for %%f in (*.sql) do %1\ProcessStoredProcedures %%f %3)© %3 %4
param #1 is path where the scripts reside
param #2 is path where the files are that need to be combined
param #3 is file name that will be created in that directory
param # 4 is the full path and name of the final file you want outputed
The above bat file calls the second bat file:
@echo off
for /f “tokens=1,2,3,4 delims=.” %%a in (“%1″) do set p1=%%a&set p2=%%b&set p3=%%c&set p4=%%d
echo /**************************************** %p1%.%p2% ***************************************/ >> %2
type %1 >> %2
echo. >> %2
echo GRANT EXECUTE on [%p1%].[%p2%] to XXX >> %2
echo GO >> %2
echo. >> %2
the “for” statement above is parsing out the Stored Procedure file name. We use the naming convention for the files the same as SQL Server names it if you have the DB script out your DB to seperate fiels for each. Example dbo.MySP.StoredProcedure.sql
So you can see here that in this bat file you can add lines before and to the end, basically what ever you want.
I hope this helps someone and thanks to everyone that posted above as it helped me get to where I got.
Thank you!
Thanks for this. I was certain COPY (or XCOPY) *.log > all.txt would work but it didn’t. I’m sure I did something like this before. But I just don’t recall. In any case, your method worked, but you have to remove the quotes (at least in Windows Vista 64-bit Home Edition).
I’m using 4NT but “type *.csv >> aggregate.txt” worked for me.
I used this trick today and I also needed to add a CRLF between each concatinated files. I accomplished that trick like so;
for %f in (*.log) do type “%f” >> aggregate.txt & echo: >> aggregate.txt
“echo:” displays a CRLF value.
This leaves an extra CRLF at the bottom of the aggregate.txt file, but in our case we can deal with it.
If the files you want to process are in subdirectories consider using this:
for /F “usebackq delims==” %f in (`dir /b /s *.log`) do type “%f” >> aggregate.txt & echo: aggregate.txt
Copy /b is a little easier.
I have a number of csv files, and I want to concatenate the data in all the csv files in to one. The copy command is working fine for me but I need to remove the header that comes in the output csv file after each csv file is concatenated.
I am using the below code in a batch file:-
@echo off> output.csv
copy /a 1_*.csv Output.csv
Please help
Testimonial,
for %f in (*.log) do type “%f” >> aggregate.txt
Works as advertised. Saved me hours researching commands and utilities.
Thanx!
I would like to add a standard text to each line of a notepad file containing almost 100 records. How do I do using command or batch files.
How do i put the filename of each individual file as part of the merged information in the concatenated file
Can someone tell me how to combine text files into one large text file so that the original filenames and dates are included on each record