PDA

View Full Version : VBA to read text file and import specific data from it to excel columns



theo95
05-10-2018, 07:28 AM
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!




22205

theo95
05-10-2018, 07:30 AM
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
05-10-2018, 08:15 AM
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
05-10-2018, 08:29 AM
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
05-10-2018, 03:15 PM
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
05-11-2018, 01:18 AM
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
05-11-2018, 07:01 AM
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