How to parse text file in excel and extract the values

Parsing a text file and importing values to MS Excel
We can use Excel VBA script to analyze a text file for a specific purpose. In this example, I have a text file which contains a list of files and their details like name, date created, file size etc. This is an extraction of DIR command in dos prompt. I am trying to find a text string "CNT" in each line and if it is present then I will extract the name of the file and store that in the Excel Cell along with the date the is created.

I have hardcoded the column here, you can modify it however you want. The first column will keep the name of the file and the second column will keep the file creation date.

Sub import_text_file_to_excel()
Dim fileHandle As Integer
Dim fileLine As String

Dim r As Integer
Dim c As Integer
r = 1
c = 1

While Not EOF(1)
Line Input #1, myline

On Error Resume Next
line_first_part = Left(myline, 40)
line_second_part = Right(myline, Len(myline) - 40)
If InStr(line_second_part, "CNT") Then
arr = Split(line_first_part, " ")
ActiveSheet.Cells(r, c).Value = arr(0)
ActiveSheet.Cells(r, c + 1).Value = line_second_part
r = r + 1
End If
Wend
Close #1
End Sub

Subscribe to my channel:
Twitter @iTwittt

[artsmith]

Thank you for including the code.

I adapted it to import tab-separated data from non-blank lines, from the file named "ifile" to the location where "row_index, col_index" is the upper left corner of the destination range:

Sub import_TSV_file_to_location(row_index, col_index, ifile)
r = row_index
Open ifile For Input As #1
While Not EOF(1)
Line Input #1, a
a = LTrim(a)
If a > "" Then
c = col_index
arr = Split(a, vbTab)
For j = 0 To UBound(arr)
Cells(r, c).Value = arr(j)
c = c + 1
Next j
r = r + 1
Else
' ignore blank lines
End If
Wend
Close #1
End Sub

[vmnuonly]

Thanks a lot bro, its working like a charm, with little adjustments in length of line_first_part and line_second_part. I watched several videos , but but didn't get such code.

[adrianl]

Thanks bro... really helpful tutorial!

[jfrposada]

I would recomend to explain the code at the same time that you are explaining what it does. This code is very useful, but it will just work with your text file. If someone has a different text file it will not work. It would have been good to explain the the "line_first_part" and "line_seconf_part"