search string in a text file

[excel length]

I need some help in extracting text from a text file using VBA.

I have a vba code that copy-paste a text file to excel and run from there on.
But recently, having lots of line and formula within the excel slows down the process.

the file contains from hundred to thousand of lines.where as I only need a portion of it.

So I wanted to rework(maybe start from scratch) on my vba code such that it will search and copy a line or array from the file instead of putting it all in the excel.

Example:

monday
apple 10 5 20 50 100
banana 12 24 10 11 15
orange 1 20 30 50 60

tuesday
apple 11 30 44 58 15
banana 78 8 52 99 15
orange 60 66 100 12 11

monday
banana 1 55 66 7 88 12
apple 1 668 10 78 896
orange 2 36 67 36 97

let's say my file contains the data above.
and I want these results:

first: search-copy-paste the array "monday"

monday
apple 10 5 20 50 100
banana 12 24 10 11 15
orange 1 20 30 50 60
monday
banana 1 55 66 7 88 12
apple 1 668 10 78 896
orange 2 36 67 36 97

second: search-copy-paste a line that contains "apple"

apple 10 5 20 50 100
apple 11 30 44 58 15
apple 1 668 10 78 896

and if possible
third: search-copy-paste a line that contains "apple" with the "day"

monday apple 10 5 20 50 100
tuesday apple 11 30 44 58 15
monday apple 1 668 10 78 896

my file is much more complicated than this, I need a code to start with.
and these files contains variable number of "fruits" and "days" so i might need some loop to scan through the whole file

Thank you.

 

[Damon Ostrander]

Re: search string in a text file - help

Hi excel length,

Are the lines that include the day of the week and lines that start with "apple" the only all you want imported to Excel? It seems to me it would be easy to write a macro that would extract just these data as the data are being read from the text file. Then you wouldn't have to import the entire file.

Damon

 

[excel length]

Re: search string in a text file - help

Hi excel length,

Are the lines that include the day of the week and lines that start with "apple" the only all you want imported to Excel? It seems to me it would be easy to write a macro that would extract just these data as the data are being read from the text file. Then you wouldn't have to import the entire file.

Damon

I actually need all, but i am hoping a solution for at least one of them and work it out from there. though it would be better if I have all the 3 sets of answer.
please take note that I might have a variable number of lines(rows) ranging from 100 to thousands.
and variable number of "days" and "fruits"

any sample code will be much appreciated.

thanks.

 

[excel length]

Re: search string in a text file - help

Private Sub DatafrmTxt()

Dim myFile As String, text As String
Dim F As Long
Dim x As Integer


myFile = "D:\sample.txt"


F = FreeFile
x = 1


Open myFile For Input As F
Do Until EOF(F)
Line Input #F, text


If InStr(Left(text, 5), "APPLE") > 0 Then
Range("A" & x).Value = text
x = x + 1
End If
Loop
Close F


End Sub

I had this code, this will import ALL text line starting with "APPLE"
but I cant figure out how to loop tru all "monday" only.

it is not necessary to work on this code alone, all other possible function is welcome.

 

[Damon Ostrander]

Re: search string in a text file - help

Hi again excel length,

I'm not sure if this is exactly what you want, but give this a try and let me know.

Private Sub DatafrmTxt()

   Dim myFile As String, text As String
   Dim F          As Long
   Dim iRow       As Long
   Dim iCol       As Integer
   Dim ValArray   As Variant
   
   myFile = "D:\sample.txt"
   
   F = FreeFile
   iRow = 1
   
   Open myFile For Input As F
   
   Do Until EOF(F)
ReadNextLine:
      Line Input #F, text
      If EOF(F) Then GoTo Done
      If text = "" Then GoTo ReadNextLine
      
      ValArray = Split(text, " ")

      Select Case LCase(ValArray(0))
         Case "monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday"
            Cells(iRow, "A") = ValArray(0)
         Case "apple"
            For iCol = 2 To UBound(ValArray) + 2
               Cells(iRow, iCol) = ValArray(iCol - 2)
            Next iCol
            iRow = iRow + 1
      End Select
      
   Loop
Done:
   
   Close F

End Sub

Damon

 

[excel length]

Re: search string in a text file - help

I actually figured it out with I think almost same code as yours.
I used the corresponding rows of the "days" as boundary.
It will only return line if the corresponding lines(i.e. line with "apples") are in between "monday" and "tuesday".

thanks anyway Damon, I might also check your code to simplify mine.


I have one more problem though,
I am using "END" statement on this procedure to reset all the counters and avoid errors when I rerun the code with different parameters,
but I can't run or loop the same procedure in multiple times from a different procedure because of that.


Sub RunAll()

if firstrun = true then
call DatafrmTxt
end if

if secondrun = true then
call DatafrmTxt
end if

end sub

when I ran the code (assuming that above is my code)
it stops in the first If statement.

I tried removing "END" , sub RunAll() works fine but messed the secondrun If statement.
Any suggestions?

 

[excel length]

Re: search string in a text file - help

I have one more problem though,
I am using "END" statement on this procedure to reset all the counters and avoid errors when I rerun the code with different parameters,
but I can't run or loop the same procedure in multiple times from a different procedure because of that.


Sub RunAll()

if firstrun = true then
call DatafrmTxt
end if

if secondrun = true then
call DatafrmTxt
end if

end sub

when I ran the code (assuming that above is my code)
it stops in the first If statement.

I tried removing "END" , sub RunAll() works fine but messed the secondrun If statement.
Any suggestions?

disregard this next question. I solved the code without using "END"
by declaring variables inside each sub instead of declaring under "option explicit".

 

[Bandit11]

Re: search string in a text file - help

Hello All!


Could this Code be modified to Loop through multiple text files within the same folder?
So if excel lengths' request in the thread above was to pull info from multiple text files and was actually needed to loop through files within a specific folder location that is listed in a cell on a Data "base" tab/sheet
let's say the folder location was listed in this "Data" tab/sheet in cell location B3 (this is an example...or any specific location it really doesnt matter). Also, then following that specific location
(B3 which is called out as the starting cell listed in a range of cells such as "B3:B252") it searched for the next 1st file name (ex: A1.txt) which would be located in cell B4, and repeat to the next in B5 (ex:A2.txt),
and the next (ex:B3.txt), etc...(ex:C1,D3,Z15.txt...) until a "B" cell that is in a listed range was actually blank and it stopped...
All of the information would be listed in a "New" tab/ sheet labelled "ExtractedTextData" with the text file name as each heading...?


Is this possible..? Or too confusing...


Also, sorry that I am posting this an older, existing thread... I am new to this...


Bandit11