none
Text To Excel RRS feed

  • Question

  • hi experts,

      I have many text files with cisco config, I need to extract data from txt file based on specific strings & save the output to excel each under its own header.

    example of config

    interface Vlan490
     description CID6.118739.10.00.100000-CentrexBusiness-WO1-28953002032-CONTR0051525995-Video
     ip address 10.28.104.233 255.255.255.248
     ip helper-address 10.105.40.88
     ip helper-address 10.104.142.88
     ip pim sparse-mode

    &

    interface Vlan60
     description ATP Internet
     ip address 91.74.162.9 255.255.255.248
     no ip redirects
     shutdown

    &

    interface Vlan495
     description CID6.161552.10.00.100000-BulgarTabac-WO#1-101279134147-CONTR0075037444-Inet
     ip address 94.206.72.117 255.255.255.252
     ip access-group 101 in
     ip ospf message-digest-key 1 md5 7 107D1C1501163C0A
     service-policy input TECOM-Policy-30MB
     service-policy output TECOM-Policy-175MB

    &

    interface Vlan1500
     description Mediatrix-Voice
     ip address 10.28.65.130 255.255.255.128
     ip helper-address 10.105.40.88
     ip helper-address 10.104.142.88
     no ip redirects
     standby 250 ip 10.28.65.129
     standby 250 priority 110

    Now to map the output for all lines under each vlan to excel file to its corresponding header in columns as an example

    VLAN_ID

    DESCRIPTION

    INT_IP_ADD

    HELPER_ADD ( need to combine the two addresses for both helper add (DHCP) like 10.105.40.88 10.104.142.88

    STDBY_ADD

    appreciare if you can provide the code for this.

    Thursday, June 20, 2019 12:09 PM

All replies

  • You can certainly do this in VBA.  It does support regular expressions.  However,  I would convert it to a CSV file in a language you are familiar with.  For example Python.  It has excellent string capabilities and better support for regular expressions.  You can simply open the CSV in Excel.
    Thursday, June 20, 2019 3:27 PM
  • The following was cobbled together in Word vba. Assuming your text file is as described, it should do the trick. You will need to create a five column worksheet in Excel with a header row starting at A1 and to insert the name and path of the workbook where indicated. The macro will prompt for your text file, which assumes an extension of txt or cfg. If it is something different, you will have to change the BrowseForFile function accordingly.

    Option Explicit
    
    Sub ExtractData()
    'Graham Mayor - https://www.gmayor.com - Last updated - 21' Jun 2019
    Const xlBook As String = "C:\Path\cfg_log.xlsx" 'The workbook'
    Const strSheet As String = "Sheet1" 'a five column worksheet with a header row'
    Dim oDoc As Document
    Dim strName As String, strValues As String
    Dim strVlan As String, strDesc As String, str_IP As String
    Dim strHelper As String, strStdby As String
    Dim oRng As Range, oFound As Range
        strName = BrowseForFile("Select the text file to process")
        Set oDoc = Documents.Open(FileName:=strName, AddToRecentFiles:=False)
        Set oRng = oDoc.Range
        oRng.InsertAfter vbCr & "&"
        With oRng.Find
            Do While .Execute(FindText:="interface Vlan")
                If Not oRng.Start = oDoc.Range.End Then oRng.MoveEndUntil "&"
                Set oFound = oRng.Paragraphs(1).Range
                oFound.End = oFound.End - 1
                strVlan = Replace(Mid(oFound, 11), Chr(13), "")
    
                Set oFound = oRng.Paragraphs(2).Range
                oFound.End = oFound.End - 1
                strDesc = Replace(Mid(oFound, 14), Chr(13), "")
    
                Set oFound = oRng.Paragraphs(3).Range
                oFound.End = oFound.End - 1
                If InStr(1, oFound.Text, "ip address") > 0 Then
                    str_IP = Replace(Mid(oFound, 13), Chr(13), "")
                Else
                    str_IP = "-"
                End If
    
                If InStr(1, oRng.Text, "ip helper-address") > 0 Then
                    Set oFound = oRng
                    oFound.Start = oFound.Start + InStr(1, oRng.Text, "ip helper-address")
                    Set oFound = oFound.Paragraphs(1).Range
                    oFound.MoveEnd wdParagraph, 1
                    oFound.Text = Replace(oFound.Text, Chr(13), "")
                    strHelper = Replace(oFound.Text, "ip helper-address ", "")
                    strHelper = Replace(strHelper, Chr(13), " ")
                Else
                    strHelper = "-"
                End If
    
                If InStr(1, oRng.Text, "standby 250 ip") > 0 Then
                    Set oFound = oRng
                    oFound.Start = oFound.Start + InStr(1, oRng.Text, "standby 250 ip ")
                    Set oFound = oFound.Paragraphs(1).Range
                    oFound.End = oFound.End - 1
                    oFound.MoveEndUntil "0123456789", wdBackward
                    strStdby = Replace(oFound.Text, "standby 250 ip ", "")
                    strStdby = Trim(Replace(strStdby, Chr(13), " "))
                Else
                    strStdby = "-"
                End If
                strValues = strVlan & "', '" & strDesc & "', '" & str_IP & "', '" & strHelper & "', '" & strStdby
                WriteToWorksheet strWorkbook:=xlBook, strRange:=strSheet, strValues:=strValues
                oRng.Collapse 0
            Loop
        End With
        oDoc.Close 0
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Function BrowseForFile(Optional strTitle As String) As String
    'Graham Mayor
    'strTitle is the title of the dialog box
    Dim fDialog As FileDialog
        On Error GoTo err_Handler
        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
        With fDialog
            .TITLE = strTitle
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "Text documents", "*.cfg,*.txt"
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then GoTo err_Handler:
            BrowseForFile = fDialog.SelectedItems.Item(1)
        End With
    lbl_Exit:
        Exit Function
    err_Handler:
        BrowseForFile = vbNullString
        Resume lbl_Exit
    End Function
    
    Private Function WriteToWorksheet(strWorkbook As String, _
                                      strRange As String, _
                                      strValues As String)
    Dim ConnectionString As String
    Dim strSQL As String
    Dim CN As Object
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                           "Data Source=" & strWorkbook & ";" & _
                           "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
        strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
        Set CN = CreateObject("ADODB.Connection")
        Call CN.Open(ConnectionString)
        Call CN.Execute(strSQL, , 1 Or 128)
        CN.Close
        Set CN = Nothing
    lbl_Exit:
        Exit Function
    End Function
    


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, June 21, 2019 3:19 AM