VBA import delimited text file to Excel

Question

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 1

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

Solution 2

Modified code to make it more clear, its working now. See code below.

Private Sub CommandButton1_Click()

Dim text As String, textline As String
Dim Cell As Range
Dim strLine() As String
Dim aCol, aRow, aNames, colNow As Long, rowNow As Long
Dim oldDate As Date, newDate As Date


Unload Me
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If myFile = False Then
Exit Sub
'MsgBox ("No File Select. Exit")

Else

'Open and read file
Open myFile For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop
Close #1

aRow = Split(textline, "|")
With ActiveSheet

    .Cells(2, 1) = Trim(Left(aRow(0), InStr(aRow(0), " ")))

    For rowNow = 1 To UBound(aRow)

        aCol = Split(aRow(rowNow), ",")
        For colNow = 0 To UBound(aCol)
            ActiveSheet.Cells(rowNow + 1, colNow + 2) = aCol(colNow)
        Next
    Next

End With
End If
End Sub

Comments

[rei123]

Hi, Thank you for the reply. Thank you for the reply. Im getting a type mismathc error on this line : .Cells(2, 1) = Format(DateValue(aRow(0)), "d/m/yy")

[Winterknell]

@Peh, you're right and you're wrong. You're right to highlight date issues, but my local date format is d/m/y and the date in the data is "27/2/2017 17:14:32". So my date calculation works for me and also fits the data, which is d/m/y. If the data had been y/m/d or m/d/y I would have calculated the date a different way. Incidentally, DATESERIAL is not a panacea, because it depends on prior knowledge - namely which part of the date is the year, which is the month, and which is the day. If a data date is going to vary in its format, someday you'll end up stuffing the year into the day anyway!

[Winterknell]

At any rate, the solution I've chosen here is simply to strip the time from the timestamp and present the raw date, which OP can massage as he chooses. The rest really is up to the student!

[rei123]

I'm still getting the Type mismatch error after I added the declarations

[Winterknell]

Have you updated the date line? .Cells(2, 1) = Trim(Left(aRow(0), InStr(aRow(0), " ")))

[rei123]

Thanks! missed that one out, but im still not getting what I want it to be. This is what im getting now: i.stack.imgur.com/8Lvu5.jpg

[Winterknell]

You've got a pipe ("|") visible in your latest output (as "0|0"), so whatever you're doing the splits aren't working right. That's the very first split in my code: aRow = Split(sLine, "|") Since your data is not split at the pipes, it's all going into one column.

[rei123]

I just posted my code above. Is my code structured right? the date is not entered now