PDA

View Full Version : Appending multiple text files to each other by name.



cblake843
10-09-2015, 05:21 AM
I've got a number of text files I wish to append to each other by name i.e. zzzz .txt to zzzz 2002 to 2015.txt. I have the following code to do it one file at a time but would like to automate it so as each file in the folder named zzzz.txt is appended to zzzz 2002 to 2015.txt , zzzz1 to zzzz1 2002 to 2015.txt , zzzz2 to zzzz2 2002 to 2015.txt etc etc etc.

Here is the code to do it one at a time:-



Dim SourceNum As Integer
Dim DestNum As Integer
Dim Temp As String
Dim dest As String
Dim source As String


dest = Me.txtXLFIle
source = Me.txtXLFIle2


' If an error occurs, close the files and end the macro.
On Error GoTo ErrHandler


' Open the destination text file.
DestNum = FreeFile()
Open dest For Append As DestNum


' Open the source text file.
SourceNum = FreeFile()
Open source For Input As SourceNum


' Include the following line if the first line of the source
' file is a header row that you do now want to append to the
' destination file:
' Line Input #SourceNum, Temp


' Read each line of the source file and append it to the
' destination file.
Do While Not EOF(SourceNum)
Line Input #SourceNum, Temp
Print #DestNum, Temp
Loop
MsgBox "COMPLETED" & source
CloseFiles:


' Close the destination file and the source file.
Close #DestNum
Close #SourceNum
Exit Sub


ErrHandler:
MsgBox "Error # " & Err & ": " & Error(Err)
Resume CloseFiles
End Sub

Any suggestions on how to modify it to meet my requirements?

cblake843
10-11-2015, 11:12 PM
Why has this been moved to excel help. It is an access script ???

snb
10-12-2015, 12:12 AM
No matter:

this suffices to append all txt-files in folder 'G:\OF' into 1 file 'G:\zusammen.txt'



Sub M_snb()
shell "cmd /c copy G:\OF\*.txt G:\zusammen.txt",0,true
End sub

You can run this code in any Office application.

cblake843
10-12-2015, 12:16 AM
Thanks for the reply. I don't want to append them all in one file. I want to append them to different files i.e. if a file is named xyz 2015.txt then the file with the name xyz.txt appends to it or abc 2015.txt then file abc.txt gets appended to it. This could be up to 18 files at one time. Is this possible ?

snb
10-12-2015, 12:29 AM
Ganz einfach:

Sub M_snb()
shell "cmd /c copy G:\OF\xyz*.txt G:\xyz_2015.txt",0,True
End Sub

cblake843
10-12-2015, 01:17 AM
Thanks again I get the following error:-

Compile error :
Wrong number of arguments or invalid property assignment.

snb
10-12-2015, 02:05 AM
Sub M_snb()
shell "cmd /c copy G:\OF\xyz*.txt G:\xyz_2015.txt"
End Sub

nor the files nor the paths may contain any spaces ! otherwise:


Sub M_snb()
shell "cmd /c copy ""G:\OF\xyz*.txt"" ""G:\xyz_2015.txt"""
End Sub

cblake843
10-12-2015, 04:05 AM
Thanks it sort of works but not as I wished. When copying xyz.txt to xyz_2015.txt , the first file xyz.txt simply overwrites xyz_2015.txt and does not append the data. Instead of cmd /c copy should it be append or something else ?

SamT
10-12-2015, 08:40 AM
Why has this been moved to excel help. It is an access script ???
I moved it because the procedure is pure VBA and will run the same in any MS Application, AND, many more Experts look at the Excel Folder than any other.

SamT
10-12-2015, 08:54 AM
Correct me if I am wrong, please.

Your folder has many *_2015.txt files (Ex: xyz_2015.txt) and also has only 1 *.txt file (Ex: xyz.txt) per *._2015.txt file.

IOW, The Directory list resembles

abc_2015.txt
def_2015.txt
ghi_2015.txt
ghi.txt
jkl_2015.txt
jkl1.txt <-- ich glaube nein
jkl2.txt <-- ich glaube nein
jkl3.txt <-- ich glaube nein
xyz_2015.txt
xyz.txt

cblake843
10-12-2015, 11:06 PM
[QUOTE = velvet; 332100] Correct me if I am wrong, please. YourFolder has many * _2015.txt files (Ex: xyz_2015.txt) and therefore has only 1 * .txt file (Ex: xyz.txt) per * ._2015.txt file. IOW, The Directory list Resembles abc_2015.txt def_2015.txt ghi_2015.txt ghi.txt jkl_2015.txt jkl1.txt <- I do not think so jkl2.txt <- I do not think so jkl3.txt <- - I do not think so xyz_2015.txt xyz.txt [/ QUOTE]


For every large xxx_2015.txt file there is a smaller xxx.txt file which I want to append to the larger one. I can do this one at a time but it is very laborious and want to to automate it somehow. The directory will hold files like this

xxx_2015.txt
xxx.txt
xyx_.2015.txt
xyx.txt
zzz_2015.txt
zzz.txt
zyz_2015.txt
zyz.txt

The files with the first 3 characters matching should be appended. Any ideas ?

snb
10-13-2015, 12:20 AM
Genau was meine Code macht.

SamT
10-13-2015, 12:42 AM
In a Sub Use Dir to loop thru the 2002 to 2015 files

DestFile = Dir Path\*2002*
Do while DestFile <> ""
Parse out the Common part of the name (zzzz)
SoucreFile = Path\zzzz.txt
DestFile = Pathzzzz & 2002 t0 2015.txt
Cmd copy /Y DestFile + SourceFile DestFile
Loop the Dir Function


Copies one or more files to another location.

COPY [/D] [/V] [/N] [/Y | /-Y] [/Z] [/A | /B ] source [/A | /B]
[+ source [/A | /B] [+ ...]] [destination [/A | /B]]

source Specifies the file or files to be copied.
/A Indicates an ASCII text file.
/B Indicates a binary file.
/D Allow the destination file to be created decrypted
destination Specifies the directory and/or filename for the new file(s).
/V Verifies that new files are written correctly.
/N Uses short filename, if available, when copying a file with a
non-8dot3 name.
/Y Suppresses prompting to confirm you want to overwrite an
existing destination file.
/-Y Causes prompting to confirm you want to overwrite an
existing destination file.
/Z Copies networked files in restartable mode.

The switch /Y may be preset in the COPYCMD environment variable.
This may be overridden with /-Y on the command line. Default is
to prompt on overwrites unless COPY command is being executed from
within a batch script.

To append files, specify a single file for destination, but multiple files
for source (using wildcards or file1+file2+file3 format).