Text File Parsing Not Working


New Member
Feb 23, 2018
I have a text file that I am trying to parse into Excel. The file fields are delimited by |. When I use the code below, it parses everything in to the first column. Can anyone help with how to modify the code so that it parses each line of the text file into the appropriate columns and rows?

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long
Dim x As Integer
Dim y As Integer

Delimiter = "|"
FilePath = Application.GetOpenFilename
rw = 0

'Open the text file in a Read State
TextFile = FreeFile
Open FilePath For Input As TextFile

'Store file content inside a variable
FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
Close TextFile

'Separate Out lines of data
LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable
For x = LBound(LineArray) To UBound(LineArray)
If Len(Trim(LineArray(x))) <> 0 Then
'Split up line of text by delimiter
TempArray = Split(LineArray(x), Delimiter)

'Determine how many columns are needed
col = UBound(TempArray)

'Re-Adjust Array boundaries
ReDim Preserve DataArray(col, rw)

'Load line of data into Array variable
For y = LBound(TempArray) To UBound(TempArray)

DataArray(y, rw) = TempArray(y)
'Debug.Print DataArray(y, rw)

Range("Harp_Anchor").Offset(y, rw) = DataArray(y, rw)

Next y
End If

'Next line
rw = rw + 1

Next x


Well-known Member
Aug 6, 2014
Can do it this way:

' Code to import data into Excel
' from pipe-delimited text file.

' Assumes lines are separated by
' carriage return AND line feed.

Public Sub ImportData()
  Dim objFileSystem As Object
  Dim objTextStream As Object
  Dim vntFilePath As Variant
  Dim strFileText As String
  Dim astrData() As String
  Dim y As Integer
  Dim x As Long
  On Error GoTo ErrHandler
  vntFilePath = Application.GetOpenFilename("Text Files (*.txt;*.csv), *.txt;*.csv")
  If vntFilePath = False Then GoTo ExitProc
  Set objFileSystem = CreateObject("Scripting.FileSystemObject")
  Set objTextStream = objFileSystem.OpenTextFile(vntFilePath, 1)
  strFileText = objTextStream.ReadAll()
  astrData = TextToArray(strFileText, x, y)
  ThisWorkbook.Sheets.Add.Range("A1").Resize(x, y).Value = astrData
  On Error Resume Next
  Set objTextStream = Nothing
  Set objFileSystem = Nothing
  Exit Sub
  MsgBox Err.Description, vbExclamation
  Resume ExitProc
End Sub

Private Function TextToArray(ByVal strText As String, _
                             ByRef lngRows As Long, _
                             ByRef intCols As Integer) As String()
  Const strDELIMITER = "|"
  Dim astrRows() As String
  Dim astrCols() As String
  Dim c1 As Integer
  Dim c2 As Integer
  Dim r1 As Long
  Dim r2 As Long
  Dim c As Integer
  Dim r As Long
  astrRows = Split(strText, vbCrLf)
  r1 = LBound(astrRows)
  r2 = UBound(astrRows)
  astrCols = Split(astrRows(r1), strDELIMITER)
  c1 = LBound(astrCols)
  c2 = UBound(astrCols)
  ReDim astrResults(r1 To r2, c1 To c2) As String
  lngRows = r2 - r1 + 1
  intCols = c2 - c1 + 1
  For r = r1 To r2
    astrCols = Split(astrRows(r), strDELIMITER)
    For c = c1 To Application.Min(c2, UBound(astrCols))
      astrResults(r, c) = astrCols(c)
    Next c
  Next r
  TextToArray = astrResults
End Function

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...