Extract Info from Text File
I have 90,000 rows of information in an excel file and 90,000 text files. The path of the text file is in the first column of every row, and I want to extract specific data from the text file and enter this information into the specific cells.
Option Explicit
Public xR, zZ As Long, daRows
Sub getFields()
daRows = Application.CountA(ActiveSheet.Range("A:A")) 'Determine # of rows
For zZ = 2 To daRows 'Set up loop
Open Cells(zZ, 6) For Input Access Read As #6 'Open file to be read
Do While Not EOF(6)
Line Input #6, xR 'Read a line
If Left(xR, 7) = "Title: " Then
Cells(zZ, 2) = Right(xR, Len(xR) - 7)
End If
If Left(xR, 8) = "Author: " Then
Cells(zZ, 4) = Right(xR, Len(xR) - 8)
End If
If Left(xR, 9) = "Subject: " Then
Cells(zZ, 3) = Right(xR, Len(xR) - 9)
End If
Loop
Close #6 'Close the file
Next 'Move to next line
End Sub
The example I was given had specific information on 3 lines in the text document:
Title: SOMETHING
Author: UZAKOV
Subject: 1961. ZOOL. ZH., MOSCOW
So, we simply get the text path and file name from A, open it and start looking for certain phrases
at the beginning of the line. When found, write to the appropriate cell next to the path statement.
The Right(xR, Len(xR) - 8)
statement tells it to take the right side of the phrase complete except the first 8 characters, etc.
|