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
     ip helper-address
     ip helper-address
     ip pim sparse-mode


    interface Vlan60
     description ATP Internet
     ip address
     no ip redirects


    interface Vlan495
     description CID6.161552.10.00.100000-BulgarTabac-WO#1-101279134147-CONTR0075037444-Inet
     ip address
     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
     ip helper-address
     ip helper-address
     no ip redirects
     standby 250 ip
     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




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


    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), "")
                    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), " ")
                    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), " "))
                    strStdby = "-"
                End If
                strValues = strVlan & "', '" & strDesc & "', '" & str_IP & "', '" & strHelper & "', '" & strStdby
                WriteToWorksheet strWorkbook:=xlBook, strRange:=strSheet, strValues:=strValues
                oRng.Collapse 0
        End With
        oDoc.Close 0
        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.Add "Text documents", "*.cfg,*.txt"
            .InitialView = msoFileDialogViewList
            If .Show <> -1 Then GoTo err_Handler:
            BrowseForFile = fDialog.SelectedItems.Item(1)
        End With
        Exit Function
        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)
        Set CN = Nothing
        Exit Function
    End Function

    Graham Mayor - Word MVP

    Friday, June 21, 2019 3:19 AM