• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to extract Node Name and Software Name into Columns

cp713a

New Member
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

Excel Ninja
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...
Code:
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

New Member
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,
 

Attachments

Chihiro

Excel Ninja
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.
Code:
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
 

Attachments

Top