Updated 3 April 2009. Following Jimmy Pena’s comments I have revised the code to read and write the files in one operation, rather than line by line. I have also added the option to add a numbered separator line between each file in the combined file.
What is the easiest way to combine a large number of text files into a single file?
Probably the easiest way is with some obscure command line syntax, that you can never remember or find help on when you need it; but for those of us who like to things in VBA the procedure below is reasonably fast and simple to use. The procedure is:
- Get the list of file names and location, and the name for the combined file.
- Change directory to the location of the files to be combined.
- Open the combined file.
- For each file in the list:
- Open the file.
- Read the file and write it to the combined file.
- If a separator line is specified wite the line and file number to the end of the combined file
- Close the file and open the next.
- After reading and writing all the files close the combined file.
To get the list of file names into a worksheet I use the file manager Total Commander, which lets you copy the names of selected files to the clipboard.
I have added the routine Comb_Text() to the spreadsheet Text-in2.xls, which can be downloaded here.
Here is the code:
Sub Comb_Text()
Dim i As Long
Dim FNameA As Variant
Dim NumFiles As Long, FName As String, Fnum1 As Long, FNum2 As Long
Dim Wholefile As String, FPath As String, AFname As String, SepLine As String
On Error GoTo no_selection
FNameA = Selection.Value
FPath = FNameA(1, 1)
If Mid(FPath, 2, 1) = ":" Then ChDrive Left(FPath, 1)
ChDir FPath
AFname = FNameA(2, 1)
Fnum1 = FreeFile
Open AFname For Output Access Write As #Fnum1
SepLine = FNameA(3, 1)
NumFiles = UBound(FNameA)
For i = 4 To NumFiles
FName = FNameA(i, 1)
FNum2 = FreeFile
Open FName For Input Access Read As #FNum2
Wholefile = Input$(LOF(FNum2) - 1, #FNum2)
Print #Fnum1, Wholefile
If SepLine .NE. "" Then Print #Fnum1, "End of File " & i - 3 & " " & SepLine
Close #FNum2
Next i
no_selection:
Close #FNum2
Close #Fnum1
End Sub
Note that if code is copied and pasted from the blog the “” characters get corrupted, and need to be corrected in the Visual Basic Editor, or just download the spreadsheet which has open source code. Also replace “.NE.” with the usual VBA “not equal” symbol.
Here’s what it looks like:
Combine text files from an Excel list
Comb-Text input and output with separator line
Newton Excel Bach, not (just) an Excel Blog 
You can fix the “smart quotes” with a plugin:
http://coffee2code.com/wp-plugins/wpuntexturize
Also, can’t you read the entire file in one line? Something like
Dim FileNum As Long
FileNum = FreeFile
Open FName For Input As #FileNum
Dim GetText As String
GetText = Input$(LOF(FileNum), FileNum)
LikeLike
JP – thanks a lot for the tips. It seems that the plugin will only work for externally hosted blogs, but I’ll bear it in mind for if/when I can use it.
I’ll have another look at the combine routine and re-post when I get some time.
LikeLike
If you can edit your functions.php, add this line at the bottom:
LikeLike
The PHP I posted got removed, hope this works:
<?php remove_filter(‘the_content’, ‘wptexturize’); ?>
LikeLike
Jimmy – I have modified the code based on what you suggested.
I’m not sure where to find my functions.php file, but it looks like I can’t edit it without upgrading anyway.
LikeLike
I guess you can’t do it with a wordpress.com blog, you have to have a self-hosted WordPress blog. 🙄
LikeLike
in cmd.exe… browse to correct folder, open a prompt, and :
copy *.txt filename.txt
LikeLike
pythonisms,
Your code assumes the user wants to combine all the text files in a folder, not just the ones listed on the worksheet.
But — here’s how you’d implement this in VB:
Sub test()
Dim x As Double
x = Shell(“cmd.exe /c copy C:\*.txt C:\filename.txt”, 0)
End Sub
LikeLike
pythonisms – OK, I suppose “copy *.txt filename.txt” isn’t all _that_ obscure 🙂
But for selected files the VBA would be a bit quicker, and if nothing else it did give me an excuse to look at handling of text files in VBA.
Incidentally, for those who do want to do this sort of thing via the command line, Total Commander (or one of its clones) is an excellent tool because it includes both a graphical interface and an integrated command line.
Jimmy – yes looks like that, but thanks for your efforts anyway.
LikeLike
Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog