Wanting to display a text file in particular way

[SamDsouza]

Hello
My Textfile Age.Txt is in following manner where data is copied from other sources

Name Tom
Age 13

Name John
Age 15

Name Jack
16
Age

Name Michael
Age
12

Name Martin
Age 33

Name George
43
Age

Would like to display in the below following manner

Name Tom
Age 13

Name John
Age 15

Name Jack
Age 16

Name Michael
Age 12

Name Martin
Age 33

Name George
Age 43

These are just 6 records as mentioned above but there are more than 200
I am irritated to correct each record as per the output required
ie if one observes the numerical value of Age comes in previous line before word Age or it comes in next line after word Age. Would like to have the numerical value of age in the line of word Age

Any ideas for a suitable code

SamD
166

 

[Yongle]

fPath is path to Age.Txt - please amend path before running the code
The code creates a new file named Result.Txt (in the same folder as Age.Txt)

Sub CreateTextFile()
    Dim F1 As String, F2 As String, NewText As String, OldText As String
    Const fPath = "C:\Test\Folder\Subfolder"      'AMEND
    
    F1 = fPath & "\" & "Age.txt"
    F2 = fPath & "\" & "Result.txt"
    Open F1 For Input As #1
    Open F2 For Output As #2

    Do Until EOF(1)
        Line Input #1, OldText
        If OldText Like "Name*" Then NewText = OldText
        If OldText Like "Age*[0-9]*" Then NewText = OldText
        If IsNumeric(OldText) Then NewText = "Age " & OldText
        If Len(NewText) > 0 Then
            Print #2, NewText
            If NewText Like "Age*[0-9]*" Then Print #2, vbNullString
        End If
        NewText = ""
    Loop
    
    Close #1
    Close #2

End Sub

 

[SamDsouza]

Yongle Dear

Sorry for late reply

Thank you so much for your valuable input and worked perfectly.

What does this mean Like "Name*" and Like"Age*[0-9]*" and what made you opt to choose "Like" syntax.

2nd Question
Will it be possible to get the above result as per your reply #2 using fso. OpenTextFile(FileFullName). If yes then how.

I am trying to understand and explore various options

Guidance shall be appreciated

SamD
167

 

[Yongle]

What does this mean Like "Name*" and Like"Age*[0-9]*" and what made you opt to choose "Like" syntax.

WHY USE?
Like allows the use of wildcards and string patterns to be checked without being specific
- it is very flexible and reduces requirement for loops when testing for multiple characters
Read here

Like Operator - Visual Basic

Learn more about: Like Operator (Visual Basic)

docs.microsoft.com

If OldText Like "Name*"
Means If OldText begins with this string "Name"

Could have included a space before *
If OldText Like "Name *"

Which would give same result as ...
If Left(OldText, 5) = "Name "

If OldText Like "Age*[0-9]*"
Means If OldText begins "Age" followed by any characters followed by at least one number

 

[Yongle]

2nd Question
Will it be possible to get the above result as per your reply #2 using fso. OpenTextFile(FileFullName). If yes then how.

Sub CreateTextFileFSO()
    Dim oFSO As Object, oFS As Object
    Dim F1 As String, F2 As String, NewText As String, OldText As String
    Const fPath = "C:\Test\Folder\Subfolder"      'AMEND
   
    F1 = fPath & "\" & "Age.txt"
    F2 = fPath & "\" & "Result.txt"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFS = oFSO.OpenTextFile(F1)
    Open F2 For Output As #1

    Do Until oFS.AtEndOfStream
        OldText = oFS.ReadLine
        If OldText Like "Name*" Then NewText = OldText
        If OldText Like "Age*[0-9]*" Then NewText = OldText
        If IsNumeric(OldText) Then NewText = "Age " & OldText
        If Len(NewText) > 0 Then
            Print #1, NewText
            If NewText Like "Age*[0-9]*" Then Print #1, vbNullString
        End If
        NewText = ""
    Loop
    Close #1
End Sub

 

[Yongle]

Thank you so much for your valuable input and worked perfectly

Perhaps you could mark that post as a Solution

How to do that is explained here (page down until you find Mark as Solution)

October 2020 Forum Update

Mark as solution Although the "Mark as solved" concept has never been used at the MrExcel Message Board since Bill launched the forum in 1998, the new feature "Mark as solution" is different in that marking a post as a solution does not lock the thread. This allows members to offer alternative...

www.mrexcel.com

 

[SamDsouza]

Yongle

Mind Blowing Explanation. you have explained beautifully with link attached and excellent fspen File solution.

It seems fso. OpenFile name will be always coded to read a mostly a text file or data file. and not for output as what was desired.

Thankx a Tonne ?

SamD
168