Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Merge several fixed width text files into one

355 views
Skip to first unread message

XP

unread,
Feb 5, 2008, 1:05:00 PM2/5/08
to
Using Office 2003 and Windows XP; and using using VBA from MS-Excel;

1) There will be several (between 1 and 10) fixed width text files in a
specific folder, each formatted the same way; the files range from a few kb
to 100kb in size.

2) I need a program that will sequentially open each text file and copy the
contents;

3) The contents of each separate text file, along with all formatting, must
be transferred undisturbed into a new fixed width text file, appending the
contents of each text file into a new SINGLE merged text file;

I can already code the part where the list of files is captured and each may
be opened and closed. I need help with the part where the data is copied from
the text file and then written to the new single merge file. Can someone
please post some example code that would help me get started?

Should I use FileSystemObject? Or something else? Suggestions? Help?

Thanks much in advance for your assistance.

Jim Rech

unread,
Feb 5, 2008, 3:48:20 PM2/5/08
to
Adapt this:

Sub a()
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array("c:\File1.txt", "c:\File2.txt")
Open "c:\file3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1
End Sub


--
Jim
"XP" <X...@discussions.microsoft.com> wrote in message
news:85C25A4A-B954-4370...@microsoft.com...

XP

unread,
Feb 6, 2008, 10:19:01 AM2/6/08
to

I adapted your example code and it works perfectly - and fast...

Thanks much Jim!

blucap.bu...@gmail.com

unread,
Jun 13, 2017, 10:42:17 AM6/13/17
to
Hi Jim

I've used your example and it works creating a new file, but is there a way to add the text from file1.txt and file2.txt into the file3.txt.

I mean, updating the file 3 every time I run the code and don´t create a new file.

GS

unread,
Jun 13, 2017, 11:28:27 AM6/13/17
to
If this is being done correctly, each new block of data should be 'appended' to
the target file; -all of which gets done in memory using standard VBA file I/O
functions.

So once the 1st target file is created it would thereafter be re-opened to
append each new block of data. The process will create the file if it doesn't
already exist when it 'dumps' the 1st block of data into it.

Tht said, the proper way to do this is to recurse a folder for .txt files (or
whatever file extension you specify; -csv,dat,tsv...) and append them to a
single file. There already exists a free stand-alone utility (non-Excel) called
*TXTcollector* that you can download here...

https://txtcollector.en.softonic.com/

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

blucap.bu...@gmail.com

unread,
Jun 13, 2017, 12:39:04 PM6/13/17
to
Thanks a Lot Jim for the quick Reply.

In some site, they adapted your code to relatives paths this way:

---
Dim r As String
r = Application.ThisWorkbook.Path
Dim SrcFiles, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String
SrcFiles = Array(r & "\File1.txt", r & "\File2.txt")
Open r & "\File3.txt" For Output As #1
For Counter = 0 To UBound(SrcFiles)
Open SrcFiles(Counter) For Input As #2
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1

---


But As i told you, the file 3 doesn't append the text, just create a new one every time the code is ejecuted.

Som help would be great.

Also, I've note that actually, the "CurrSrc" and "DestFile" variables are declared but not used at all, they can be "erased" from the code?

GS

unread,
Jun 13, 2017, 2:28:35 PM6/13/17
to
I'm not Jim! I don't see how this code will do what you want, NOR do I see any
point *spending* the energy required to rewrite a solution that will work (I
have Lou Gehrig's) when the already existing free utility I posted a link to
does an excellent job of merging text files.

Ulrich Möller

unread,
Jun 13, 2017, 6:38:26 PM6/13/17
to
Just another idea using the oldstyle CMD Shell:

Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" >> {1}"

Dim strCopyCommand As String
Dim dblRetVal As Double

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

SyncShell "cmd.exe /c " & strCopyCommand, vbNormalFocus
End Sub

Public Function SyncShell(ByVal Cmd As String, ByVal WindowStyle As
VbAppWinStyle) As Long
SyncShell = VBA.CreateObject("WScript.Shell").Run(Cmd, WindowStyle, True)
End Function

Usage example:
MergeFiles "file1.txt file2.txt", "file3.txt"

You will be able to modify the copy cmd to your needs and you can even
use standard cmd syntax with wildcards like
MergeFiles "File*.txt" , "OutFile.txt"
and so on.

Ulrich


Ulrich Möller

unread,
Jun 13, 2017, 6:44:50 PM6/13/17
to
Am 14.06.2017 um 00:38 schrieb Ulrich Möller:
> Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
> Const COPYCMD As String = "for %f in ({0}) do type ""%f"" >> {1}"
>
> Dim strCopyCommand As String
> Dim dblRetVal As Double
>
> strCopyCommand = Replace(COPYCMD, "{0}", InFile)
> strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)
>
> SyncShell "cmd.exe /c " & strCopyCommand, vbNormalFocus
> End Sub

Sorry for the mixed data types, here the corrected version:

Public Sub MergeFiles(ByVal InFile As String, ByVal OutFile As String)
Const COPYCMD As String = "for %f in ({0}) do type ""%f"" >> {1}"

Dim strCopyCommand As String
Dim lngRetVal As Long

strCopyCommand = Replace(COPYCMD, "{0}", InFile)
strCopyCommand = Replace(strCopyCommand, "{1}", OutFile)

lngRetVal = SyncShell("cmd.exe /c " & strCopyCommand, vbNormalFocus)
End Sub

Public Function SyncShell(ByVal Cmd As String, ByVal WindowStyle As
VbAppWinStyle) As Long
SyncShell = VBA.CreateObject("WScript.Shell").Run(Cmd, WindowStyle,
True)
End Function

Ulrich


GS

unread,
Jun 13, 2017, 6:58:36 PM6/13/17
to
Hey Ulrich,
Nice to see you 'spreading your wings'!<g>

FWIW:
My experience with users for this type of utility is (typically) where a share
folder is used as a repository for collecting data from various sources over a
given period. Once per period the data gets consolidated into a single file for
processing further by some means, and the source files get deleted so the
folder only contains un-processed data files.

That said, your example could be modified to drill drown a selected folder (and
optionally, subfolders) so the user doesn't have to specify filenames. Your
sample addresses EXACTLY what the OP states is wanted; -I'm guessing that's not
the 'actual' scenario...<g>

Ulrich Möller

unread,
Jun 14, 2017, 7:36:16 AM6/14/17
to
Hi Garry,

Am 14.06.2017 um 00:58 schrieb GS:
> Nice to see you 'spreading your wings'!<g>
>
Why not? ;-)
> FWIW:
> My experience with users for this type of utility is (typically) where
> a share folder is used as a repository for collecting data from
> various sources over a given period. Once per period the data gets
> consolidated into a single file for processing further by some means,
> and the source files get deleted so the folder only contains
> un-processed data files.
>
> That said, your example could be modified to drill drown a selected
> folder (and optionally, subfolders) so the user doesn't have to
> specify filenames. Your sample addresses EXACTLY what the OP states is
> wanted; -I'm guessing that's not the 'actual' scenario...<g>

In the old days this was a classical job for some commands from the
command line. My intention was to bring this technique back to memory
and how we can use it now a days. Of course this can also be done with
some vba but what about the fun?

Ulrich

GS

unread,
Jun 14, 2017, 9:12:36 AM6/14/17
to
Ha, ha! The 'fun' only exists for the old timers, right! These days we only see
newbie OPs (which I am one of in the Classic VB forums), mostly looking for
'turn-key' solutions and so won't appreciate your offering same as I do!<g>
0 new messages