Option Explicit
Sub main()
Dim v As Variant
Dim vRet As Variant
vRet = fSplitRowsInArray("c:\Test\test.csv")
v = Application.Transpose(vRet)
With ThisWorkbook.Worksheets(1)
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(v, 1), UBound(v, 2)).Value = v
Application.Intersect(.Range("A:A"), .UsedRange).TextToColumns Destination:=.Range("A1"), Other:=True, OtherChar:="|"
End With
End Sub
Function fSplitRowsInArray(ByVal sLoadFromFile As String) As Variant
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile sLoadFromFile
fSplitRowsInArray = Replace(.readtext, vbTab, "|")
fSplitRowsInArray = Split(fSplitRowsInArray, Chr(10))
.Close
End With
End Function