Appending text files to one master file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Has another the code to merge four files together , my problem is that each
file has a different number of characters and the application it is needed
for will not allow if the lines have not got the correct amount of characters.

Thanks in advance
 
Hi Nadine,

It's not clear what you want to achieve. Do you want to

*concatenate the four input files into one output file?

*merge them (with each line of the output file consisting of all or part
of the corresponding line from each of the input files)?

*interleave them (e.g. take one line from each input file in rotation to
create the output file)?

*or something else?

Are these actual text files, or are they tables or queries in a
database?
 
Thanks for the reply.

I will try to explain further.
I have created the table in access and I transfer text out to notepad for
use with a banking application. The problem is that the first line of the
file should only be 80 characters long same for the second and third. Next
comes the detail lines which are 120 characters long , there can be more than
one detail line and finally the last line has to be 80 characters. So when I
created the table I brought the struture out the the longest width, but the
bank application crashes when it hits the spaces and on the first few lines.
Have you any thoughts as to what I could do.

Regards
 
Hi Nadine,

There are many possible approaches. By the sound of it you are able to
export a file that contains all the data you need and the only problem
is that all the lines are 120 characters long. The first three lines and
the last line are padded out to 120 characters with spaces, and you need
to truncate these lines to 80 characters.

If that's the case, the simplest thing is probably to export the file as
now, and then use VBA code to truncate the lines in question. I spent a
few minutes modifying a VBA function I'd written for a similar purpose,
and have pasted it at the end of this message.

Thanks for the reply.

I will try to explain further.
I have created the table in access and I transfer text out to notepad for
use with a banking application. The problem is that the first line of the
file should only be 80 characters long same for the second and third. Next
comes the detail lines which are 120 characters long , there can be more than
one detail line and finally the last line has to be 80 characters. So when I
created the table I brought the struture out the the longest width, but the
bank application crashes when it hits the spaces and on the first few lines.
Have you any thoughts as to what I could do.
Click to expand...



Function TruncateFirstAndLastLines( _
ByVal FileSpec As String, _
Optional ByVal BackupExtension As String = "") As Long

'Truncates the first and last lines of a text file
'Optionally leaves the original file with its extension
'changed to BackupExtension.
'Returns 0 on success, otherwise the number of the error.

Dim fso As Object 'Scripting.FileSystemObject
Dim fIn As Object 'Scripting.TextStream
Dim fOut As Object 'Scripting.TextStream
Dim fFile As Object 'Scripting.File
Dim strFolder As String
Dim strNewFile As String
Dim strBakFile As String
Dim lngNumLines As Long
Dim j As Long

Const TRUNC_LEN = 80 'length to which to truncate
Const TRUNC_HEAD = 3 'number of lines to truncate at start of file
Const TRUNC_TAIL = 1

On Error GoTo Err_TruncateFirstAndLastLines

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
'Handle relative path in Filespec
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strNewFile = .BuildPath(strFolder, fso.GetTempName)

'Scan file to count lines
Set fIn = .OpenTextFile(FileSpec, ForReading)
Do Until fIn.AtEndOfStream
fIn.SkipLine
Loop
lngNumLines = fIn.Line - 1

'Raise an error if there aren't enough lines in the file
If lngNumLines <= TRUNC_HEAD + TRUNC_TAIL Then
Err.Raise (-99)
End If

'Otherwise ...
fIn.Close

'Open files
Set fIn = .OpenTextFile(FileSpec, ForReading)
Set fOut = .CreateTextFile(strNewFile, True)

'Truncate header lines
Do Until fIn.Line = TRUNC_HEAD + 1
fOut.WriteLine Left(fIn.ReadLine, TRUNC_LEN)
Loop

'Read and write body of file
Do Until fIn.Line = lngNumLines - TRUNC_TAIL + 1
fOut.WriteLine fIn.ReadLine
Loop

'Truncate footer lines
Do Until fIn.AtEndOfStream
fOut.WriteLine Left(fIn.ReadLine, TRUNC_LEN)
Loop


fOut.Close
fIn.Close

'Rename or delete old file
If Len(BackupExtension) > 0 Then
strBakFile = .GetBaseName(FileSpec) _
& IIf(Left(BackupExtension, 1) <> ".", ".", "") _
& BackupExtension
If .FileExists(.BuildPath(strFolder, strBakFile)) Then
.DeleteFile .BuildPath(strFolder, strBakFile), True
End If
Set fFile = .GetFile(FileSpec)
fFile.Name = strBakFile
Set fFile = Nothing
Else
.DeleteFile FileSpec, True
End If

'Rename new file
Set fFile = .GetFile(strNewFile)
fFile.Name = .GetFileName(FileSpec)
Set fFile = Nothing
Set fso = Nothing

End With
'normal exit
TruncateFirstAndLastLines = 0
Exit Function
Err_TruncateFirstAndLastLines:
TruncateFirstAndLastLines = Err.Number
End Function
 
Thanks for all the help John But I am still having issues the code is
blanking my file. I set it up as a module and then I run it on a macro. I
must be doing something wrong . Code is pasted ,would you mind looking at it
for me . Thanking you in advance.

Nadine

Function TruncateFirstAndLastLines()

'Truncates the first and last lines of a text file
'Optionally leaves the original file with its extension
'changed to BackupExtension.
'Returns 0 on success, otherwise the number of the error.

Dim fso As Object 'Scripting.FileSystemObject
Dim fIn As Object 'Scripting.TextStream
Dim fOut As Object 'Scripting.TextStream
Dim fFile As Object 'Scripting.File
Dim strFolder As String
Dim strNewFile As String
Dim strBakFile As String
Dim lngNumLines As Long
Dim j As Long

Const TRUNC_LEN = 80 'length to which to truncate
Const TRUNC_HEAD = 3 'number of lines to truncate at start of file
Const TRUNC_TAIL = 1

On Error GoTo Err_TruncateFirstAndLastLines

Set fso = CreateObject("Scripting.FileSystemObject")

With fso
'Handle relative path in Filespec
FileSpec = .GetAbsolutePathName("C:\General\Bankfl.txt")
strFolder = .GetParentFolderName("C:\General\Bankfl.txt")
strNewFile = .BuildPath(strFolder, fso.GetTempName)

'Scan file to count lines
Set fIn = .OpenTextFile("C:\General\Bankfl.txt", 1)
Do Until fIn.AtEndOfStream
fIn.SkipLine
Loop
lngNumLines = fIn.Line - 1

'Raise an error if there aren't enough lines in the file
If lngNumLines <= TRUNC_HEAD + TRUNC_TAIL Then
Err.Raise (-99)
End If

'Otherwise ...
fIn.Close

'Open files
Set fIn = .OpenTextFile("C:\General\Bankfl.txt", 1)
Set fOut = .CreateTextFile("C:\General\Bankfl.txt", True)

'Truncate header lines
Do Until fIn.Line = TRUNC_HEAD + 1
fOut.WriteLine Left(fIn.ReadLine, TRUNC_LEN)
Loop

'Read and write body of file
Do Until fIn.Line = lngNumLines - TRUNC_TAIL + 1
fOut.WriteLine fIn.ReadLine
Loop

'Truncate footer lines
Do Until fIn.AtEndOfStream
fOut.WriteLine Left(fIn.ReadLine, TRUNC_LEN)
Loop


fOut.Close
fIn.Close

'Rename or delete old file
If Len(BackupExtension) > 0 Then
strBakFile = .GetBaseName("C:\General\Bankfl.txt") _
& IIf(Left(BackupExtension, 1) <> ".", ".", "") _
& BackupExtension
If .FileExists(.BuildPath(strFolder, strBakFile)) Then
.DeleteFile .BuildPath(strFolder, strBakFile), True
End If
Set fFile = .GetFile("C:\General\Bankfl.txt")
fFile.Name = strBakFile
Set fFile = Nothing
Else
.DeleteFile FileSpec, True
End If

'Rename new file
Set fFile = .GetFile(strNewFile)
fFile.Name = .GetFileName("C:\General\Bankfl.txt")
Set fFile = Nothing
Set fso = Nothing

End With
'normal exit
TruncateFirstAndLastLines = 0
Exit Function
Err_TruncateFirstAndLastLines:
TruncateFirstAndLastLines = Err.Number
End Function
 
Nadine,

There was no need to alter the code I posted, and doing so has broken
it.

Try this, in the VBA editor:

1) Delete the modified code.

2) In the Tools menu, select Options, then select the Editor tab of the
Options dialog. Make sure that Require Variable Declaration is checked.

3) Insert a new module (a standard module, not a class module). Paste
the function I posted into the module, and save the module (let's call
it vbMiscFunctions).

4) To call the TruncateFirstAndLastLines() function from a macro, you
need to create a VBA function that can itself be called from a macro.
This can go in the same module. It could looke like this:

Public Function TruncateLinesInMyFile()
Dim RetVal As Long

RetVal = TruncateFirstAndLastLines("C:\General\Bankfl.txt", "bak")
If RetVal = 0 Then
MsgBox "File processed OK.", vbOKOnly + vbInformation
Else
MsgBox "Error & " RetVal & "processing file.", _
vbOKOnly + vbInformation
End If

End Function
 
Hi John,

Thanks for all you help on this, it works a treat. Just one problem I
changed the file extension to EMT and i have to run the code twice for it to
work, any ideas ? Again thanks for all the help.

Nadine
 
Back
Top