Excel VBA - search and import multiple nested strings from text file

Question

I am attempting to extract a few strings from a json data text file. The catch is that the info I need is not under unique headers and is nested. As you can see from the below sample, each blocksize has 3 iothroughput numbers. How do I go about retrieving them? I can search for a string and return a row number, but I am unable to consistently get the correct data and its corresponding block size.

blocksize:16kb
initial:
iothroughput:500
overwrite:
iothroughput:1000
read:
iothroughput:2000
blocksize:64MB
initial:
iothroughput:10
overwrite:
iothroughput:20
read:
iothroughput:30

So far, I have the following which searches for a string and returns the line. I would like to then begin another search at that point for "initial", return that line number, and search for "iothroughput" and pull that value into a cell.

Sub Import_File()

MsgBox ("Please Insert Data Contention File")

myFile = Application.GetOpenFilename()

Open myFile For Input As #1

Do Until EOF(1)

    Line Input #1, textline

    Text = Text & textline

Dim Ctr, Ctr2, Str

Ctr = Ctr + 1

   Str = 16777216

   If textline Like "*" & "" & Str & "" & "*" Then

        Ctr2 = Ctr

        Range("E4").Value = Ctr2

   End If

Loop

Close #1

If you are trying to parse a JSON, perhaps [this](https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop) and [this](https://stackoverflow.com/questions/6627652/parsing-json-in-excel-vba) may be of use — cybernetic.nomad, Sep 17 '18 at 21:23

score 0 · Answer 1 · answered Sep 17 '18 at 21:33

If that data is in Column A, one thing you could do is this kludgy IF() statement. Put in a cell next to one with "blocksize:" and drag down:

=IF(LEFT(A1,LEN("blocksize:"))="blocksize:",SUBSTITUTE(A3,"iothroughput:","")&", "&SUBSTITUTE(A5,"iothroughput:","")&", "&SUBSTITUTE(A7,"iothroughput:",""),"")

_{Note: This is a "dumb" formula and assumes you have exactly three iothroughput numbers per blocksize and the pattern does not change.}

Excel VBA - search and import multiple nested strings from text file

1 Answers1