VBA to read text file and import specific data from it to excel columns

[theo95]

Hello,

I have tried to develop a code in VBA in which a text file is loaded and tried by reading line by line to find a specific word that is repeated in it. I would like to extract the values corresponding to it and store them in an excel column. I have tried this with Mid and Right functions as well as the Instr but apparently I am doing something wrong. I am new to coding so any help would be much appreciated.

I will be extracting info such as LaneId SectionId etc.. It should be the same code for all more or less but if i manage to do it correctly for one, doing the rest must be easy!




test.xlsm

[theo95]

I have uploaded the excel file. I cannot upload the txt file for some reason but it contains the following:

<SizeClassificationReport Start="2018-04-27T12:33:52.903+01:00" End="2018-04-27T12:34:53.993+01:00" TimePeriod="1" xmlns="ICDNAV001-SizeClassificationReport"><Classifications><Classification CarriageWayId="1" LaneId="0" SectionId="17800" Classification="Combination" Count="0" AverageSize="0" AverageSpeed="0" /><Classification CarriageWayId="1" LaneId="0" SectionId="17800" Classification="Long" Count="0" AverageSize="0" AverageSpeed="0" />

[Paul_Hossler]

I have uploaded the excel file. I cannot upload the txt file for some reason but it contains the following:

<SizeClassificationReport Start="2018-04-27T12:33:52.903+01:00" End="2018-04-27T12:34:53.993+01:00" TimePeriod="1" xmlns="ICDNAV001-SizeClassificationReport"><Classifications><Classification CarriageWayId="1" LaneId="0" SectionId="17800" Classification="Combination" Count="0" AverageSize="0" AverageSpeed="0" /><Classification CarriageWayId="1" LaneId="0" SectionId="17800" Classification="Long" Count="0" AverageSize="0" AverageSpeed="0" />

That sort of looks like an XML file, with each piece separated by a NL char.

If it is an XLM file, there are some special (but more complicated) ways of reading/extracting

Try renaming the file as CSV or TXT to upload it

[theo95]

Hello Paul,

Thank you for your time!
I have tried these but it wont let me upload saying it is an invalid file.

The contents of the text file are indeed in xml. However because the file includes what I have added in the second comment but repeated a lot of times it would be hard work to convert it to xml so i thought if i would treat it as the text file it is and try and read characters it would be easier. I have been given these data like this so I can only work on them the best way I can.

I lack the knowledge unfortunately and I feel I am really close to achieving it but theres something wrong that will not read the words I want. (Always returns "not found" .I have set ithis through the code but only for words that do not exist!)


Do you think you might know what I am doing wrong??

[Paul_Hossler]

Assuming that the wt.txt file looks like this (separate lines) ….

<SizeClassificationReport Start="2018-04-27T12:33:52.903+01:00" End="2018-04-27T12:34:53.993+01:00" TimePeriod="1" xmlns="ICDNAV001-SizeClassificationReport">

<Classifications>
<Classification
CarriageWayId="1"
LaneId="0"
SectionId="17800"
Classification="Combination"
Count="0"
AverageSize="1234"
AverageSpeed="0"
 />

<Classification
CarriageWayId="1"
LaneId="0"
SectionId="17800"
Classification="Long"
Count="0"
AverageSize="0"
AverageSpeed="0"
 />

… something like this might be easier, but ...

Sub test2()
   MsgBox getString()
End Sub

Public Function getString() As String
    Dim myFSO As New Scripting.FileSystemObject
    Dim path As String
    Dim fileName As String
    Dim testStr As String
    Dim v As Variant
    
    path = Environ("USERPROFILE") & "\Desktop\"
    fileName = "wt.txt"
    testStr = "AverageSize"
    
    Set FSO = myFSO.OpenTextFile(path + fileName)
    
    Do Until FSO.AtEndOfStream
        txt = Trim(FSO.ReadLine)
        
        If Len(txt) > 0 Then
           v = Split(txt, "=")
        
           If v(0) = testStr Then
                getString = v(1)
                Exit Function   '   delete to keep on going
           End If
        End If
     
     Loop
     FSO.Close
End Function

… since your "AverageSpeed" occurs multiple times in the file, I'm not sure how you want to handle them

[theo95]

Thank you very much!! this actually works.. my only issue though is that the text file I have is what i have just sent but repeated a million times. So it would be really hard for me to separate each line. is there any way I can separate/parse this text file through vba before proceeding to the code you have sent me?

[Paul_Hossler]

Thank you very much!! this actually works.. my only issue though is that the text file I have is what i have just sent but repeated a million times. So it would be really hard for me to separate each line. is there any way I can separate/parse this text file through vba before proceeding to the code you have sent me?

1. Again, I'd / we'd have to see the file to be sure.

2. It depends on what you want to get out. "AverageSize" is repeated many times; what do you want to do