Way to search for a string in text file, pull out everything until another string?

omahadivision · #1 11-21-2013, 04:28 PM

Hi Guys,

I am trying to think of a way to use Excel to pull data out of a text file. I would want all the data after a certain line but before another line. The starting and ending line would always be the same, but the line numbers and amount of data between the two lines would be different. I would want to pull data line by line without using delimiters, as the data I am pulling from uses a space differently than Excel.

Any tips? This is some coding I've come up with so far. It will only tell me the row that the string I am searching for is on:

Code:

Open Fname For Input As #1
  iRow = 1
  Do While Not EOF(1)
     Input #1, A
     If A = "1990" Then
    MsgBox iRow
     End If
     iRow = iRow + 1
  Loop
  Close 1

BobBridges · #2 11-21-2013, 08:31 PM

Hi, omaha. So you want to start processing the contents of the file when you get to a line that's equal to "1990", and stop when you get to one that has ... well, something else? Sure, shouldn't be a problem. But what do you want to do with the data? In your program you're displaying the line number where "1990" first appears, and that's a start. But once there, do you want to copy the data to another file, save it in an Excel worksheet, display it one line at a time in a MsgBox, what?

omahadivision · #3 11-21-2013, 08:35 PM

Thanks for the response! My goal is to have the values fill certain cells. I figure I can do that using Range pretty good. Although the data will be taken from different parts of the text files, the place on the Excel sheets should be about the same. I know the number of possible data points that the files could have, so I'd leave enough space for the biggest text file size. My goal is to extract data from the rather arcane software output file and make it into a spreadsheet. I would do it just one text file at a time.

BobBridges · #4 11-22-2013, 11:27 AM

You didn't ask any actual questions

, so I'm having to guess, but it sounds like you're saying you know how to transfer the values (once you find them) from the file to the worksheet, using Range. And you've already demonstrated that you can read a file and detect certain values in it. Yet you're here asking for help, so there must be some part of the program you don't know how to write. But what is it? I want to help—automation is one of the funnest things I do, and helping someone else learn it is right up there too. But I need a hint.

Or maybe you need a hint: You can see that you're not sure how to proceed, but you can't see what questions to ask. So let me try leading:

1) Does the value "1900" go into your worksheet, or is it just a trigger that tells you the data you want to save will be on the next line?

2) Once you have a line with data you want to save, do you want to put the whole line into a worksheet cell, or just part of it? If part of it, what VBA statements will you use to extract just the part you want?

3) Once you have the data you want to put in the worksheet, how will you decide which row and column to store them in? And what VBA statement will you use for that purpose?

See what you can do with those questions; that'll either clarify for you how to write the program, or clarify for me what kind of help you need. Maybe both.

omahadivision · #5 11-22-2013, 11:51 AM

My bad! I should have clarified my post more.

How do I pull line by line while ignoring delimiters? And how do I have the import start after the string I am looking for and then finish when it comes across another predefined string?

In answer of the questions:

1. 1900 does not need to go in. Just after it.
2. All of 1 line=1 cell. That is my main difficulty; Excel automatically tries to delimit it using the code above.
3. I will figure out the exact cells later, but they will always be the same cells.

BobBridges · #6 11-22-2013, 12:56 PM

Out of habit, for I/O in VB I use the FileSystemObject rather than the native Basic statements for I/O—the Open, Input and Close statements, I mean [see footnote]. But judging by your results, what you've done so far will work fine, so unless you decide you want to switch to the fso, let's go with what you have so far. Let's start with your program as it is so far:

Code:

Open Fname For Input As #1
jr = 0
Do While Not EOF(1)
  Input #1, A
  jr = jr + 1
  If A = "1990" Then MsgBox jr
  Loop
Close 1

I changed one of the variable names because of my own prejudices, but the basic structure is the same. Now for your question: There are always more than two ways to skin a cat, in programming, but here are the two obvious ways that come to me immediately. First, you could run two different loops:

Code:

Open Fname For Input As #1

' First find the starting statement.
Do While Not EOF(1)
  Input #1, A
  If A = "1990" Then Exit Do
  Loop
If EOF(1) then Exit Sub ' "1990" did not appear

' Now handle the subsequent data.
Do While Not EOF(1)
  Input #1, A
  ' insert statements here to put the value of A into various cells.
  Loop
Close 1

There's nothing wrong with this method, but sometimes I like to use a switch instead; until we've run into the starting trigger ("1990", I mean) the switch is off, and after that it's on again. This allows us to use the same loop, if you like that idea:

Code:

Open Fname For Input As #1
fData = False
Do While Not EOF(1)
  Input #1, A
  If fData Then
    ' insert statements here to put the value of A into various cells.
  ElseIf A = "1990" Then
    fData = True
    End If
  Loop
Close 1

In this version, fData starts as False, but as soon as you run into "1900" the program changes it to True. Whenever fData is True, the program executes your statements to copy the data to your worksheet; the rest of the time it just checks to see whether "1900" has appeared yet.

None of the above takes into account the ending trigger; I expect you can work that out yourself, given the above hints. But feel free to ask further if you need more help.

---

[footnote:] The FileSystemObject gives you a lot of control over your PC's drives, folders and files. But the differences in this logic are pretty minor. Here's your program, as I would have written it:

Code:

Open Fname For Input As #1
jr = 0
Do While Not EOF(1)
  Input #1, A
  jr = jr + 1
  If A = "1990" Then MsgBox jr
  Loop
Close 1

If you're curious, the equivalent using the fso would look like this:

Code:

Set fo = fso.OpenTextFile(Fname, 1) 'ForReading
jr = 0
Do While Not fo.AtEndOfStream
  A = fo.ReadLine
  jr = jr + 1
  If A = "1990" Then MsgBox jr
  Loop
fo.Close

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to create new word doc and save the file using String found in the document	VBNation	Word VBA	2	02-08-2013 07:14 AM
Find and replace a string of text	errtu	Word	1	01-31-2013 02:09 PM
Excel Fomula to search for a string and display value from different column	zeeshanbutt	Excel	1	07-29-2012 12:48 AM
Convert numbers to a specific text string	francis	Excel	1	10-06-2011 01:43 PM
Extract numbers from a text string	aleale97	Excel	4	02-10-2011 10:33 AM