How to import a length variable text file to excel with VBA and split it in several columns?

This is my first question for this community.

I would like to know how to import a length variable text file (from a bash script) to Excel with VBA and split it in several columns. I'm learning to use VBA and cannot figure it out yet. This is what I have:

Servers list from script:

Servers list from script

This the output I need in excel:

Servers arranged

The fields "Day" and "%Idle" will be added by the VBA macro.

The numbers from 6 to 11 will be added manually, so there is no problem there.

Thanks in advance and kind regards to everyone!

1 answer

  • answered 2018-10-15 21:29 chillin

    Maybe something like this will work (depending on how much the structure of your data/servers list varies).

    You will need to change the value of FILE_PATH in the function splitTextFileByServer (below) to wherever it is on your machine.

    Option Explicit
    
    Private Sub WriteServerListToSheet()
        Dim logsSplitByServer() As String
        logsSplitByServer = splitTextFileByServer()
    
        Dim interimArray() As String
        Dim outputArray() As Variant: ReDim outputArray(1 To 8, 1 To 2)
    
        With ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
            Dim serverIndex As Long
            Dim readIndex As Long
            Dim outputColumnOffset As Long
    
            For serverIndex = LBound(logsSplitByServer) To UBound(logsSplitByServer)
                interimArray = VBA.Strings.Split(logsSplitByServer(serverIndex), vbNewLine, -1, vbBinaryCompare)
                If (UBound(interimArray) + 1) <> UBound(outputArray) Then ' (+1) as interimArray is 0-based
                    ReDim outputArray(1 To (UBound(interimArray) + 2), 1 To 2) '(+2) as 0-based and we want a row for headers: Day, %Idle
                End If
    
                ' Assume first item in interimArray is: server***
                outputArray(1, 1) = interimArray(LBound(interimArray))
                outputArray(2, 1) = "Day"
                outputArray(2, 2) = "%Idle"
    
                For readIndex = (LBound(interimArray) + 1) To UBound(interimArray)
                    outputArray(2 + readIndex, 2) = interimArray(readIndex) ' (2 + ... as we want to skip first 2 rows
                Next readIndex
    
                .Offset(0, outputColumnOffset).Resize(UBound(outputArray, 1), UBound(outputArray, 2)).Value2 = outputArray
                outputColumnOffset = outputColumnOffset + 3 ' Change this if you decide to add columns
            Next serverIndex
    
        End With
    
    End Sub
    
    Private Function splitTextFileByServer() As String()
        Const FILE_PATH As String = "C:\Users\USER\Desktop\serverslist.txt"
    
        If Len(VBA.FileSystem.Dir$(FILE_PATH)) = 0 Then
            MsgBox ("No file exists at: " & FILE_PATH)
            Exit Function
        End If
    
        Dim fileContents As String
        ' Read file into memory; assumes it will fit.
        Open FILE_PATH For Binary Access Read As #1
        fileContents = VBA.Strings.Space$(LOF(1))
        Get #1, 1, fileContents
        Close #1
    
        Dim outputArray() As String
        outputArray = VBA.Strings.Split(fileContents, "########################################" & vbNewLine, -1, vbBinaryCompare)
    
        splitTextFileByServer = outputArray
    End Function
    

    If it doesn't work, let me know why/what's wrong with the output.