I personally would not choose to process such large files with VBA - as you have hinted I would pre-process the text file (I would use C# as my preference) to produce a CSV file that could then just be loaded directly into Excel.
However, you have tagged VBA and you have also said that you are not familiar with it, so here is a simple method of loading your file using VBA.
You will need to add in specific information about which parts of the file you want and where to put them in the spreadsheet. Unfortunately you haven't given us enough detail, but hopefully I've made it clear what goes where - look out for "TODO:"
Note: This is not very performant. There are also more elegant ways of doing this but I'm trying to keep it simple.
1. Create the code that follows in a module in Excel (Alt-F11 will bring up the Visual Basic editor. In the left-hand pane you will see your workbook name, right-click on that and select "Insert" then select "Module"
2. You are going to open your text file and read it line by line. For each line you will determine whether or not it is required (or can be discarded), if so then you will copy data from the line into the spreadsheet ... here is the code to read the file from end to end
Sub ReadFile()
Dim fileName As String
Close 'close any open files
'TODO: Change this line to locate your file
fileName = "c:\temp\example.txt"
Dim fileNum As Integer
fileNum = FreeFile
Open fileName For Input As #fileNum
Dim aLine As String
Dim rowNum As Integer
rowNum = 3 'TODO: the row number you want the data to start on
Do While Not EOF(fileNum)
Line Input #fileNum, aLine
If WantLine(aLine) Then ProcessTheLine aLine, rowNum
Loop
Close #fileNum
End Sub
3. The function
WantLine
will determine whether or not you can ignore the line or whether you want to process it ...
Function WantLine(aLine As String) As Boolean
Dim retValue As Boolean
retValue = False
If Mid$(aLine, 1, 12) = "MAC Address:" Then retValue = True
If Mid$(aLine, 1, 12) = "Port :" Then retValue = True
If Mid$(aLine, 1, 12) = "TimeStamp :" Then retValue = True
WantLine = retValue
End Function
4. The subroutine
ProcessTheLine
starts pulling a line of text apart for the data ...
Sub ProcessTheLine(aLine As String, rowNum As Integer)
If Mid$(aLine, 1, 12) = "MAC Address:" Then
SetDataForCol 1, rowNum, aLine, 14, 15
SetDataForCol 2, rowNum, aLine, 49, 20
End If
If Mid$(aLine, 1, 12) = "Port :" Then
SetDataForCol 3, rowNum, aLine, 14, 15
SetDataForCol 4, rowNum, aLine, 49, 20
End If
If Mid$(aLine, 1, 12) = "TimeStamp :" Then rowNum = rowNum + 1
End Sub
5.Finally, the subroutine
SetDataForCol
will extract the data from the line at the position provided for the length provided and insert it into the spreadsheet at the location provided
Sub SetDataForCol(col As Integer, rowNum As Integer, aLine As String, start As Integer, slen As Integer)
Dim data As String
data = Trim$(Mid$(aLine, start, slen))
Cells(rowNum, col) = data
End Sub