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:
This the output I need in excel:
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!
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_PATHin 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.