extract specific lines above and below in a text file and place them into excel worksheet

[Tom Mililli]

Hi,
I would like to read a text or xlm file, 3 - 4 rows above and below the MAC Addresses found in each file plus the the s/n info.There could be as many as 11 MAC Addresses per file but only 1 computer serial number.
Would prefer to have this info collected from mulitple files be appended to one master excel spreadsheet, if not easily doable, then a spreadsheet per file will suffice.

I'm not very familiar with the Offset command, unless there is a better way in accomplishing this

Here is a example.

S/N 12345
..
..
..
.
Ethernet
Port 56823
MAC Address
etho
2 slot
.
.
.
.
.
Port 56819
MAC Address
eth1
4 slot 6
.
.
.
.
Any assistance would be much appreciated.
Tom
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

Few questions.

1. Can you upload sample xml file? It would be easier to work with than text, since it's structured document.

2. How should the out put look like?

3. Are all text files in same folder?

 

[Tom Mililli]

Hi,
I have included the output excel sheet on (2nd tab). On the first tab are lines that I wanted extracted from .csv file. Plus, included the source file .csv in the zip, since the html wasn't able to modify its values.

The .csv files would be located in one folder, that I would like to read and append into the 1 excel spreadsheet on tab2

Any assistance would be greatly appreciated.
Tom
P.S. I have changed the MAC addresses, etc. for security reasons.

 

[Chihiro]

Couple of additional clarification needed.

1. ethX - Controller/Slot: Some are missing info in your output. Is this intended?
2. Is the text file structure always going to be same? I.E. Same row contain same info always?

 

[Tom Mililli]

Good Day Chihiro,

1. ethX - Controller/Slot: Some are missing info in your output. Is this intended?
Yes. I figured you understood what was needed.

2. Is the text file structure always going to be same? I.E. Same row contain same info always?
Yes. The info and rows needed should be consistent.

 

[jindon]

Tom Mililli

Your csv file has many same Serial Number/Product Name etc.
Which part do you want to extract specifically?
If you highlight the lines in txt file with MSWord or rtf format, it will be clearer.

 

[jindon]

You can compare the results,

Sub test()
    Dim fn As String, txt As String, m As Object, i As Long, x As String
    Dim a(), t As Long, e, temp As String, SL As Object
    fn = Application.GetOpenFilename("CSVFiles,*.csv")
    If fn = "False" Then Exit Sub
    Set SL = CreateObject("System.Collections.SortedList")
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    ReDim a(1 To 2, 1 To 1000): t = 6
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Global = True: .MultiLine = True
        .Pattern = "^(Serial Number|Product Name|Processor Package [12]|Total memory),(.*)"
        If .test(txt) Then
            For i = 0 To 4
                a(1, i + 1) = .Execute(txt)(i).submatches(0)
                a(2, i + 1) = .Execute(txt)(i).submatches(1)
            Next
        End If
        .Pattern = "^""(.*?)"",(.*)\r\n(.*\r\n){1,3}MAC Address,(.+)\r\n.*\r\n.*?,(.*\d+)$"
        For Each m In .Execute(txt)
            x = GetSortVal(m.submatches(4))
            SL(x) = Array(Array(m.submatches(4), m.submatches(3)), _
                    Array(m.submatches(4) & "-Description/Port", m.submatches(1)), _
                    Array(m.submatches(4) & "-Contoller/Slot", m.submatches(0)))
        Next
        For i = 0 To SL.Count - 1
            For Each e In SL.GetByIndex(i)
                t = t + 1: a(1, t) = e(0): a(2, t) = e(1)
            Next
        Next
        .Pattern = "^(iLO),(.*)\r\n(.+\r\n)*?ilo port status,(.*)\n(.*\r\n)*?MAC Address,(.*)"
        For Each m In .Execute(txt)
            t = t + 1: a(1, t) = m.submatches(0): a(2, t) = m.submatches(5)
            t = t + 1: a(1, t) = m.submatches(0) & "- Description/Port": a(2, t) = m.submatches(1)
            t = t + 1: a(1, t) = m.submatches(0) & "-Controller/Slot": a(2, t) = m.submatches(3)
        Next
        .Pattern = "^""((Hard|Logical) Drives? \d+), (.*?)"",""(.*)"""
        t = t + 1
        For Each m In .Execute(txt)
            If temp = "" Then
                temp = m.submatches(0)
            Else
                If m.submatches(0) = temp Then Exit For
            End If
            t = t + 1: a(1, t) = m.submatches(0): a(2, t) = m.submatches(3)
            t = t + 1: a(1, t) = m.submatches(0) & "-Controller/Slot": a(2, t) = m.submatches(2)
        Next
    End With
    Sheets("output format").Range("c9").Resize(2, t).Value = a
End Sub

Private Function GetSortVal(ByVal txt As String) As String
    Dim i As Long, m As Object
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+"
        If .test(txt) Then
            For i = .Execute(txt).Count - 1 To 0 Step -1
                Set m = .Execute(txt)(i)
                txt = Application.Replace(txt, m.firstindex + 1, _
                m.Length, Format$(m.Value, "000000000000"))
            Next
        End If
        GetSortVal = txt
    End With
End Function

 

[Chihiro]

Nice! I was about to hard code row#s.
I've got to learn use of RegEx in VBA.

 

[Tom Mililli]

Hi,
regarding the data rows hightlighted. I did that in the excel file (first tab has the rows hightlighted in yellow of the rows desired.
The good news is that although there is many serial number rows, its the first occurrence that is what's used.
Hope this clears things up.
Tom