Edit Txt file data

[Chewyhairball]

Hi

I have thousands of lines of data comprised of the 2 types below and I would like to edit the numbers so they are to 1 decimal place.

<Test:Module>2</Test:Module> would become <Test:Module>2.0</Test:Module>
<Testb:Moduleb>4.2222</Testb:Moduleb> would become <Testb:Moduleb>4.2</Testb:Moduleb>

I currently import the data from a txt file into column A Sheet 1.
Sheet 2 has formulas in column A to do this task which I then copy and paste into a new txt file.

So my questions are

1. can I do this using VBA before copying and pasting back into a txt file and if so how.
2. Can I use vba to alter the txt file directly and if so how.

Thanks for any help with this.
Thank Rory

 

[Chewyhairball]

I was playing with this, I had assumed you wanted to edit the text file without ever importing it into cells in Excel.
The below checks that the word "test" and "module" exist on the line, it then edits that line so the number is formatted as "0.0".
It does so in an array and the spits the array back out into the text file. Nothing is ever imported to Excel.

Sub EditTxtFile()
    Dim fPath As String, fCont As Variant, fNum As Integer
    Dim x As Long, str As String, var As Variant

    fPath = "C:\Users\jbloggs\Desktop\test.txt" ' text file location, change to suit
    fNum = FreeFile
    Open fPath For Input As #fNum
    fCont = Split(Input(LOF(fNum), fNum), vbNewLine)
    For x = 0 To UBound(fCont)
        str = fCont(x)
        If InStr(str, "Test") > 0 And InStr(str, "Module") > 0 Then
            var = Split(Replace(str, ">", "<"), "<")
            fCont(x) = "<" & var(1) & ">" & Format(var(2), "0.0") & "<" & var(3) & ">"
        End If
    Next x
    Close #fNum
   
    Open fPath For Output As #1
    Print #fNum, Join(fCont, vbNewLine)
    Close #fNum
End Sub

This works great! I am going to be cheeky now and ask if something else is possible. I maybe should have started with this but I wasnt sure if it was possible.

The text file that I am altering actually comes from an xml file produced from a database. For various reasons both the database and xml files cannot be altered and the only thing I can do is manually open the xml as txt file edit that then save as an xml. Both of your suggestions have saved me lots of headaches but I am wondering now if the opening the xml file as txt can be done.

1.Open xml file as txt
2.Convert data to 1 decimal place(which you have done)
3.Save back to xml file

appologies if I should be starting another thread for this.

 

[Georgiboy]

The xml file should work in the same way the txt file does, try is as below:

Sub EditTxtFile()
    Dim fPath As String, fCont As Variant, fNum As Integer
    Dim x As Long, str As String, var As Variant

    fPath = "C:\Users\jbloggs\Desktop\test.xml" ' xml file location, change to suit
    fNum = FreeFile
    Open fPath For Input As #fNum
    fCont = Split(Input(LOF(fNum), fNum), vbNewLine)
    For x = 0 To UBound(fCont)
        str = fCont(x)
        If InStr(str, "Module") > 0 Then
            var = Split(Replace(str, ">", "<"), "<")
            fCont(x) = "<" & var(1) & ">" & Format(var(2), "0.0") & "<" & var(3) & ">"
        End If
    Next x
    Close #fNum
    
    Open fPath For Output As #1
    Print #fNum, Join(fCont, vbNewLine)
    Close #fNum
End Sub

 

[Chewyhairball]

Assuming they are all to be 1 decimal place try this. For testing, assumes data in col A and results in col B.

Sub Edit_To_1_DP()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    p1 = InStr(s, ">")
    p2 = InStr(2, s, "<")
    a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

If it is just the 'Module' lines then try

Sub Edit_To_1_DP_v2()
  Dim a As Variant
  Dim i As Long, p1 As Long, p2 As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = a(i, 1)
    If InStr(1, s, "Module", vbTextCompare) > 0 Then
      p1 = InStr(s, ">")
      p2 = InStr(2, s, "<")
      a(i, 1) = Left(s, p1) & Format(Val(Mid(s, p1 + 1)), "0.0") & Mid(s, p2)
    End If
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

Just a minor alteration to include 'or'

If InStr(1, s, "Module", vbTextCompare) > 0 Or InStr(1, s, "moduleb", vbTextCompare) > 0 Then

and to include a /

 p2 = InStr(2, s, "</")

and it does exactly what I wanted. Brilliant. thank you

 

[Chewyhairball]

The xml file should work in the same way the txt file does, try is as below:

Sub EditTxtFile()
    Dim fPath As String, fCont As Variant, fNum As Integer
    Dim x As Long, str As String, var As Variant

    fPath = "C:\Users\jbloggs\Desktop\test.xml" ' xml file location, change to suit
    fNum = FreeFile
    Open fPath For Input As #fNum
    fCont = Split(Input(LOF(fNum), fNum), vbNewLine)
    For x = 0 To UBound(fCont)
        str = fCont(x)
        If InStr(str, "Module") > 0 Then
            var = Split(Replace(str, ">", "<"), "<")
            fCont(x) = "<" & var(1) & ">" & Format(var(2), "0.0") & "<" & var(3) & ">"
        End If
    Next x
    Close #fNum
    
    Open fPath For Output As #1
    Print #fNum, Join(fCont, vbNewLine)
    Close #fNum
End Sub

Hi

This worked perfect and has now given me a tool to literally save me doing anything.

thanks

 

[Chewyhairball]

I want to say a massive thank you to both of you for giving me a working solution. I can see me using both of them at some point, not just for my question today.
I am always impressed when I come on here and this time is no exception.

Thank you guys