Need to extract Node Name and Software Name into Columns

[cp713a]

Hi,

I'm fairly new to this forum and I need your help. I have a text file with huge data. It contains Node Names and Software Names. Something like this:

Node Name: A1
Total software found: 100
Software Name: a1
Version: 1xxx
etc...
more software name...
Node Name: B1
etc..
more software name
and more software name
etc....
....and so on

What's the VBA to extract the name of each Node Name (set as new column header) and read in all the software name under the Node Name until the next Node Name and set it as a new column and all of its software name until there are no more lines to read in.

Any help would really appreciate it.

 

[Chihiro]

Hard to give you exact code without more concrete sample of what the raw data looks like. 25 to 50 line sample that clearly demonstrate your issue will work.

General concept would be to use Freefile() to read all content fo text file in one shot. Then split using "Node Name:". Then using resulting array, process further if needed.

To read text file as one...

Sub Demo()
Dim intFF as Integer: intFF = FreeFile()
Dim iFile as String: iFile = "C:\Folder\Filename.txt"
Dim ar
Open iFile For Input As #intFF
strContent = Input(LOF(intFF), intFF)
Close #intFF
ar = Split(strContent, "Node Name:")
'Do something with ar
End Sub

Depending on text file size, VBA may not be suitable, or you may need to split file into smaller one.

 

[cp713a]

Hi Chihiro - Thank you for your response, attached is a sample file. The list of software name for each node can be random, some node can have 20 and some can have up to hundreds if not thousands of software names..
As for the file size, it's about 5-10MB in size.

Thanks in advance,

 

[Chihiro]

Ok, so it's not too large. I was expecting few hundred Mb or more.
Assuming that your text file is like attached.

Something like below. Run this code while you have blank sheet active.
Change iFile string to actual file path.

Sub Demo()
Dim intFF As Integer: intFF = FreeFile()
Dim iFile As String: iFile = "C:\Test\Sample.txt"
Dim i As Long
Dim ar, col
Open iFile For Input As #intFF
strContent = Input(LOF(intFF), intFF)
Close #intFF
ar = Split(strContent, "NodeName: ")

For i = 1 To UBound(ar)
    col = Split(ar(i), Chr(10))
    Cells(1, i).Resize(UBound(col)) = Application.Transpose(col)
Next

End Sub

 

[cp713a]

Chihiro, this is perfect. It's exactly of what I was looking for.

Thank you so very much