VBA import delimited text file to Excel

I'm trying to import text file to excel using vba. The text file I have has all the data in one line and contains two delimiters "|" and ",". Where, "," will separate the data into columns and "|" separate the data to rows.

I have got a code, but it seems like doing the opposite and because I'm very new to vba, I couldn't really figure out where it goes wrong.

I was thinking if there is other ways of doing to vba, something will performs like, if it recognize the delimiter it will move the data to specified cell?

Here is what my text file looks like.

27/2/2017 17:14:32 | 54,11,6,32,58,83,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,69,8,86,0,241,255 | 0,71,69,404,0,553,0 | 15,0,0,0,53,0,0 | 0,0,0,0,0,0,0 | 0,867,2,18,0,939,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 16,0,0,0,0,0,85 | 647,509,18,82,18,670,85 | 1433,0,0,0,0,0,0 | 1432,882,0,0,0,939,0 | 32,861,1,20,0,938,0 | 0,887,0,0,0,939,0 | 0,886,0,0,0,939,0 | 12,801,4,42,0,912,0 | 0,867,0,0,0,939,0 | 0,0,0,0,0,0,0 | 0,890,0,0,0,939,0 | 0,871,0,0,0,930,85 | 0,891,0,0,0,939,0 | 0,892,0,0,0,939,0 | 0,894,0,0,0,939,0 | 0,895,0,0,0,954,0 | 0,0,0,0,0,0,0 | 0,905,0,0,0,954,0 | 0,792,6,35,0,897,85 | 4,697,40,202,0,952,0 | 0,640,13,108,0,807,0 | 0,0,0,0,507,0,0 | 60,24,23,211,1128,296,0 | 4,81,16,148,569,348,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 77,224,22,367,159,791,0 | 118,42,1,7,1051,104,0 | 58,0,0,0,654,0,0 | 260,0,0,0,642,0,0 | 172,0,0,0,1241,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 114,0,0,0,1284,0,0 | 0,0,0,0,1429,0,0 | 0,0,0,0,1353,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1432,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 86,89,1,51,1279,141,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1433,0,0 | 0,0,0,0,1434,0,0 | 0,0,0,612,751,613,0 | 0,0,2,662,0,710,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,0,0 | 0,0,0,0,0,3,0 |

Here is my code

Private Sub CommandButton1_Click()

Dim sPath As String, sLine As String
Dim oFile As String
Dim i As Long
Dim workRange As Range
Dim destCell As Range


Set destCell = Range("A1")
Set workRange = Range("A1" & ":" & Range("A1").End(xlDown).Address)

Unload Me
oFile = Application.GetOpenFilename()

i = 1

Open oFile For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
    Input #1, sLine ' Read data

    i = i + 1
    Range("A" & i).Formula = sLine  ' Write data line


Loop
Close #1 ' Close file.


'Text to Columns
    With workRange
    .TextToColumns Destination:=destCell, DataType:=xlDelimited, _
     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
     Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
     FieldInfo:=Array(1, 1), TrailingMinusNumbers:=False
    End With

Application.ScreenUpdating = True
End Sub

I want to the result to look like this

Solution

Edited to remove a date objection and declare my variables

Dim aCol, aRow, aNames, colNow As Long, rowNow As Long, sLine As String
'Text to Columns
aRow = Split(sLine, "|")
With ActiveSheet
    .Cells(2, 1) = Trim(Left(aRow(0), InStr(aRow(0), " ")))
    '.Cells(2, 1) = Format(DateValue(aRow(0)), "d/m/yy")
    For rowNow = 1 To UBound(aRow)
        aCol = Split(aRow(rowNow), ",")
        For colNow = 0 To UBound(aCol)
            Sheet1.Cells(rowNow + 1, colNow + 2) = aCol(colNow)
        Next
    Next
End With

Oh, edited to add the column headers:

aNames = Array("Date and Time", "John", "Kate", "Sean", "Stephen", "Brian", "Philip", "Peter")
For colNow = 0 To UBound(aNames)
    ActiveSheet.Cells(1, colNow + 1) = aNames(colNow)
Next