VBA : Extracting string from text file and place it in the excel sheet

[aishwarya gowda]

VBA: split string by random number of spaces and extract string in particular position:

I have a a lengthy text file(which is the output of engineering sofware) with multiple lines in it.
sample of my text file : test1.txt
#
[keyword] 1140.0-1200.0 sec 100 MB 100 km/sec
[keyword] 1080.0-1200.0 sec 200 MB 17.1 km/sec
[keyword] 1020.0-1200.0 sec 300 MB 160 km/sec
[keyword] 960.0-1200.0 sec 400 MB 15.9 km/secs
#

Difficulties:
1.The number of spaces are random in the line.
2.Need to extract the 6th string in each line (each string is separated by spaces)
ex: 100
17.1
160
15.9
3.Excel template is already ready, Need to place the values extracted in a 2nd column, sample template looks like when the values are added is attached:
4. starting row : 4, column number: 2
5.split is used to convert string into an array, but unable to proceed further.

code sample i have : to fetch file from the path
"
>>> use code - tags <<<

Sub test()
  Dim ReadData As String
  Dim myFile As String
  ' Column
  Dim vntCC As Variant: vntCC = Array(2)
  ' Row Array
    Dim vntFR As Variant: vntFR = Array(4)


  myFile = "C:\Users\test1.txt"
  Open myFile For Input As #1

  Do Until EOF(1)

    Line Input #1, ReadData
    Call WriteLineToColumn(ReadData)
  Loop
End Sub
"
code sample 2: used split to convert string into array
"
Sub WriteLineToColumn(s As String)
'Converts the string of data to an array
'iterates the array and puts non-empty elements in to successive rows within Column(col)

Dim r As Long 'row counter
Dim dataElement As Variant
Dim i As Long 'array element counter
Dim t As Long 'Total records counter
Dim strLine As String 'current line in text file
For i = LBound(Split(s, " ")) To UBound(Split(s, " "))
    dataElement = Trim(Split(s)(i))
    If Not dataElement = vbNullString Then
        r = r + 1
        Range(r, col).Value = dataElement
    End If
Next

End Sub

"

 

[vletm]

aishwarya gowda
Hint: Instead of picture
- You should upload a sample Excel-file with clear sample data as well as wanted correct results.
- as well as sample of Your test1.txt-file

 

[aishwarya gowda]

Hi, thanks for the suggestion :
1. I have included my actual bit of test1.txt file which contains some data , i need to extract the values that are in Mbits/sec.
2. Need to place these values in Excel sheet, where the template is already ready.(template excel sheet attached, where the expected values are added in the column :2)

Note:
I have tried by giving the target position to extract the values in VBA macro , but the spaces in lines are uneven , could not achieve the expectation.
So, now tried to use split to break the line into array of string and extract the string which is in the sixth(6th) position i.e, values in Mbits/sec.

 

[vletm]

aishwarya gowda
Test this sample
- follow instructions to get results in wanted column

 

[Marc L]

but the spaces in lines are uneven

Hi,​

​

just using an Excel basics worksheet function :​

Sub Demo1()
    Dim V, R&, L&, W()
        V = Application.GetOpenFilename("Text Files (*.txt), *.txt"):  If V = False Then Exit Sub
        R = FreeFile
        Open V For Input As #R
        V = Application.Trim(Split(Input(LOF(R), #R), vbCrLf))
        Close #R
        L = UBound(V)
        While V(L) = "":  L = L - 1:  Wend
        ReDim W(1 To L, 0)
        For R = 1 To L:  W(R, 0) = Split(V(R))(5):  Next
        Sheet1.[C2].Resize(L) = W
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[shrivallabha]

This is one more way to read information through RegExp

Public Sub ReadTextFileRegEx()
Const ForReading = 1
Dim strInput
Dim i As Long

'\\ Use FileSystemObject Object to read contents of text file in one go
Dim objFSO As Object: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objTXT As Object: Set objTXT = objFSO.OpenTextFile("YOUR TEXT FILE PATH HERE!", ForReading)
strInput = Split(objTXT.ReadAll, vbCrLf)
objTXT.Close

'\\Create Regulax Expressions object
Dim objRgEx As Object: Set objRgEx = CreateObject("VBScript.RegExp")
objRgEx.Global = True
objRgEx.Pattern = "[^\s]+" '\\Match any character except whitespace

'\\Loop through all lines
For i = LBound(strInput) To UBound(strInput)
    Set objmatches = objRgEx.Execute(strInput(i))
    If objmatches.Count > 5 Then
        Cells(i + 2, "B").Value = objmatches(5).Value
    End If
Next i

End Sub

 

[aishwarya gowda]

aishwarya gowda
Test this sample
- follow instructions to get results in wanted column

Thank you, it worked well .