Concatenate text files

[gwarning!]

Goal: Have multiple text files, each of various lengths, be
concatenated together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change
from day to day, I need to be able to dynamically generate a batch
file to concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try
this method I thought that I would ask if others have hints, tricks,
or especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

gwarning! ?? -- any relation to the band GWAR (God, What A Racket)?

Too bad we don't use the Un*x o/s - then could use the 'cat' command;
otherwise...

If all the *.txt files in one folder where what you're after you could
do this (from VBA):

shell("type ""c:\my documents\*.txt"" >> final.txt",0)

The TYPE command displays the contents of the indicated file(s) to the
screen. If we use the append symbol ">>" it will redirect the
contents of the indicated file(s) to the last file name, "final.txt."

You can use partial file names with wildcards (* or ?) in the command:

shell("Type ""C:\My Documents\Account*.txt"" >> AllAccounts.txt",0)

This will concat all files beginning with "Account" into the file
"AllAccounts.txt."

Put quotes around any path that has a file/folder name with spaces.
If inside the Shell() function use 2 double-quotes, as shown above.

There are other ways - I'll let others tell of those.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5XTJoechKqOuFEgEQJqRwCfTay5Nxx9vS+Tw6nLHaovm9 k1szcAoKbY
KUNcGKgTqD3x6ItXx71C6yKx
=bFhU
-----END PGP SIGNATURE-----
gwarning! wrote:

Goal: Have multiple text files, each of various lengths, be concatenated
together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change from
day to day, I need to be able to dynamically generate a batch file to
concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try this
method I thought that I would ask if others have hints, tricks, or
especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg

[Fred]

I know U want to use dos, but if U know a little VB, U can open a project
and create a .exe with no forms. Then just code in simple basic commands to
open file, read rec, string fields,,,, and write rec...to create new file. U
would have all the string functions available.

Fred

"MGFoster" <[e-mail]> wrote in message
news:Cq***************@newsread3.news.pas.earthlin k.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

gwarning! ?? -- any relation to the band GWAR (God, What A Racket)?

Too bad we don't use the Un*x o/s - then could use the 'cat' command;
otherwise...

If all the *.txt files in one folder where what you're after you could
do this (from VBA):

shell("type ""c:\my documents\*.txt"" >> final.txt",0)

The TYPE command displays the contents of the indicated file(s) to the
screen. If we use the append symbol ">>" it will redirect the
contents of the indicated file(s) to the last file name, "final.txt."

You can use partial file names with wildcards (* or ?) in the command:

shell("Type ""C:\My Documents\Account*.txt"" >> AllAccounts.txt",0)

This will concat all files beginning with "Account" into the file
"AllAccounts.txt."

Put quotes around any path that has a file/folder name with spaces.
If inside the Shell() function use 2 double-quotes, as shown above.

There are other ways - I'll let others tell of those.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5XTJoechKqOuFEgEQJqRwCfTay5Nxx9vS+Tw6nLHaovm9 k1szcAoKbY
KUNcGKgTqD3x6ItXx71C6yKx
=bFhU
-----END PGP SIGNATURE-----
gwarning! wrote:

Goal: Have multiple text files, each of various lengths, be concatenated
together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change from
day to day, I need to be able to dynamically generate a batch file to
concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try this
method I thought that I would ask if others have hints, tricks, or
especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg

[gwarning!]

MGF,

Thanks for your response. I have the following:

Private Sub cmdConcatenate_Click()
On Error GoTo Err_cmdConcatenate_Click
Dim TargetFile As String
Dim FileToCopy As String

TargetFile = "o:\production\rawdata\final.ext"

Set fs = Application.FileSearch
With fs
.LookIn = "o:\production\rawdata\"
.filename = "*.0ck"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If i = 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
Call Shell("type " & FileToCopy & " > " & TargetFile, 0)

End If
If i > 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
Call Shell("type " & FileToCopy & " >> " & TargetFile, 0)
End If
Next i

Else
MsgBox "There were no files found. Batch consolidation will
be cancelled.", vbOKOnly, "Batch Consolidation Cancelled"
End If
End With

Exit_cmdConcatenate_Click:
Exit Sub

Err_cmdConcatenate_Click:
MsgBox Err.Description
Resume Exit_cmdConcatenate_Click

End Sub

Since The first iteration i need to initialize the final.ext file,
then append to it in subsequent iterations. What I think is my
problem is that the quotes might be off in the shell function, because
when i step through the task in the debugger, the vars resolve to the
correctly, but i killed in the shell statement. maybe i am quoting
wrong? yours and others help is appreciated.
thanks, greg

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

gwarning! ?? -- any relation to the band GWAR (God, What A Racket)?

Too bad we don't use the Un*x o/s - then could use the 'cat' command;
otherwise...

If all the *.txt files in one folder where what you're after you could
do this (from VBA):

shell("type ""c:\my documents\*.txt"" >> final.txt",0)

The TYPE command displays the contents of the indicated file(s) to the
screen. If we use the append symbol ">>" it will redirect the
contents of the indicated file(s) to the last file name, "final.txt."

You can use partial file names with wildcards (* or ?) in the command:

shell("Type ""C:\My Documents\Account*.txt"" >> AllAccounts.txt",0)

This will concat all files beginning with "Account" into the file
"AllAccounts.txt."

Put quotes around any path that has a file/folder name with spaces.
If inside the Shell() function use 2 double-quotes, as shown above.

There are other ways - I'll let others tell of those.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5XTJoechKqOuFEgEQJqRwCfTay5Nxx9vS+Tw6nLHaovm9 k1szcAoKbY
KUNcGKgTqD3x6ItXx71C6yKx
=bFhU
-----END PGP SIGNATURE-----
gwarning! wrote:

Goal: Have multiple text files, each of various lengths, be
concatenated together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change
from day to day, I need to be able to dynamically generate a batch
file to concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try
this method I thought that I would ask if others have hints, tricks,
or especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thoughts:

I've found that sometimes the Shell() function ignores the DOS append
symbol (>>). I've got around this by creating a batch file (.bat)
with the append commands I wanted to run & running the batch file from
the Shell() function.

==

I believe (not sure) you can use this command to concat all files in
one fell swoop:

type o:\production\rawdata\*.0ck >> final.ext

That way you won't have to loop thru each individual file name.

==

You only need to use the append symbol (>>), you don't have to first
redirect (> [which deletes any previous file of the target's name]) &
then append. If there already exists a file w/ the target's name, you
can delete it before the append command.

==

In VBA you may wish to concat the path to the FileToCopy: e.g.:

dim CopyPath As String
CopyPath = "o:\production\rawdata\"

' Set up the TargetFile as a concat of the CopyPath & the
' final file name.
TargetFile = CopyPath & "final.ext"

Then in the Shell call do this:

Call Shell("type " & CopyPath & FileToCopy & " > " & TargetFile, 0)

==

When doing a Shell call I usually prefer to create the complete
command string & then put it in the Shell. E.g.:

Dim strCommand as String
strCommand = "type " & CopyPath & FileToCopy & " >> " & TargetFile

Call Shell(strCommand,0)

That way, during Debug, I can see if the command string is constructed
correctly. Also, I can copy the command string & paste it into a DOS
window to see if it runs. Sometimes the command will run in the DOS
window but not in the Shell! Sad, but true.

==

You're VBA commands may be running faster than the DOS type command,
which means you're code must wait until the Shell function call
completes. See this URL for a code that waits for the Shell function
to complete:

http://www.mvps.org/access/api/api0004.htm
HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5cDKYechKqOuFEgEQLXoQCgyjDstEDEd2Aa9rqEvfbIFN mwAUoAoNqz
1S4lOQI6S0tvlORt0BhFjp52
=f7ZR
-----END PGP SIGNATURE-----
gwarning! wrote:

MGF,

Thanks for your response. I have the following:

Private Sub cmdConcatenate_Click()
On Error GoTo Err_cmdConcatenate_Click
Dim TargetFile As String
Dim FileToCopy As String

TargetFile = "o:\production\rawdata\final.ext"

Set fs = Application.FileSearch
With fs
.LookIn = "o:\production\rawdata\"
.filename = "*.0ck"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
If i = 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
Call Shell("type " & FileToCopy & " > " & TargetFile, 0)

End If
If i > 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
Call Shell("type " & FileToCopy & " >> " & TargetFile, 0)
End If
Next i

Else
MsgBox "There were no files found. Batch consolidation will be
cancelled.", vbOKOnly, "Batch Consolidation Cancelled"
End If
End With

Exit_cmdConcatenate_Click:
Exit Sub

Err_cmdConcatenate_Click:
MsgBox Err.Description
Resume Exit_cmdConcatenate_Click

End Sub

Since The first iteration i need to initialize the final.ext file, then
append to it in subsequent iterations. What I think is my problem is
that the quotes might be off in the shell function, because when i step
through the task in the debugger, the vars resolve to the correctly, but
i killed in the shell statement. maybe i am quoting wrong? yours and
others help is appreciated.
thanks, greg

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

gwarning! ?? -- any relation to the band GWAR (God, What A Racket)?

Too bad we don't use the Un*x o/s - then could use the 'cat' command;
otherwise...

If all the *.txt files in one folder where what you're after you could
do this (from VBA):

shell("type ""c:\my documents\*.txt"" >> final.txt",0)

The TYPE command displays the contents of the indicated file(s) to the
screen. If we use the append symbol ">>" it will redirect the
contents of the indicated file(s) to the last file name, "final.txt."

You can use partial file names with wildcards (* or ?) in the command:

shell("Type ""C:\My Documents\Account*.txt"" >> AllAccounts.txt",0)

This will concat all files beginning with "Account" into the file
"AllAccounts.txt."

Put quotes around any path that has a file/folder name with spaces.
If inside the Shell() function use 2 double-quotes, as shown above.

There are other ways - I'll let others tell of those.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5XTJoechKqOuFEgEQJqRwCfTay5Nxx9vS+Tw6nLHaovm9 k1szcAoKbY
KUNcGKgTqD3x6ItXx71C6yKx
=bFhU
-----END PGP SIGNATURE-----
gwarning! wrote:

Goal: Have multiple text files, each of various lengths, be
concatenated together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change
from day to day, I need to be able to dynamically generate a batch
file to concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try
this method I thought that I would ask if others have hints, tricks,
or especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg

[gwarning!]

Here's my solution:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim FileToCopy As String
Dim RemLine As String
Dim BatLine As String
Dim File As String
Dim BatchNumberOnly As String

Set fs = Application.FileSearch

RemLine = "REM v:\fits\consol.bat created from " _
& "FITS.frmConsolidate.cmdConcatenate"

DoCmd.RunSQL ("Delete * from tblFiles;")

With fs
.LookIn = "o:\production\rawdata\"
.filename = "*.0?k"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
File = Mid(.FoundFiles(i), 23, 11)
BatchNumberOnly = Mid(.FoundFiles(i), 26, 4)
Path = .LookIn
DoCmd.RunSQL ("INSERT INTO tblFiles (Name, File, Path,
BatchNumberOnly) VALUES (' " & .FoundFiles(i) & "','" & File & "','" &
Path & "','" & BatchNumberOnly & "');")

If i = 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
BatLine = "type " & FileToCopy & " >
o:\production\rawdata\final.ext"
Open "v:\fits\consol.bat" For Output As #1
Print #1, RemLine
Print #1, BatLine
End If

If i > 1 Then
MsgBox .FoundFiles(i)
FileToCopy = .FoundFiles.Item(i)
BatLine = "type " & FileToCopy & " >>
o:\production\rawdata\final.ext"
Print #1, BatLine
End If

If i = .FoundFiles.Count Then
Close #1
Shell "v:\fits\consol.bat", 0

End If
Next i

Else
MsgBox "There were no files found. Batch consolidation will
be cancelled.", vbOKOnly, "Batch Consolidation Cancelled"
End If
End With

MsgBox "Batch consolidation is complete. Thanks!", vbOKOnly,
"Batch Consolidation Complete"
Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
gwarning! wrote:

Goal: Have multiple text files, each of various lengths, be concatenated
together into one final consolidated text file.

Problem: Since the names of the files to be concatenated can change from
day to day, I need to be able to dynamically generate a batch file to
concatenate these files. So far, I have looked at using
FileSearch.FindFiles.Count to determine the number of files so that I
can loop through the files that exist in a directory, and I am
considering using PUT statement to generate the file. Before I try this
method I thought that I would ask if others have hints, tricks, or
especially code that I could be pointed to.

Example: In DOS, it would take the form:
copy a.txt + b.txt + c.txt + d.txt final.txt
Thanks in advance for your help. Greg