Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-21-2013, 04:28 PM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default Way to search for a string in text file, pull out everything until another string?

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
Reply With Quote
  #2  
Old 11-21-2013, 08:31 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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?
Reply With Quote
  #3  
Old 11-21-2013, 08:35 PM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 11-22-2013, 11:27 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #5  
Old 11-22-2013, 11:51 AM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 11-22-2013, 12:56 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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
Reply With Quote
  #7  
Old 11-22-2013, 08:25 PM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Thank you. I am almost there! I got the endpoint figured out and it works great! My last question: My data is still imported with delimiters, IE a comma in the text file would mean that the data from one line goes to two separate cells. I only want lines in the text file to be imported, with the comma included. What can I do to ignore all the delimiters?
Reply With Quote
  #8  
Old 11-22-2013, 09:24 PM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

<Chuckle> Remember I said I'm not used to using native Basic commands for I/O? That's getting in the way, here. I'm not sure, but I think you're saying this:
Quote:
Some of the lines I want have commas in them. For example, some of them say "45", but others say "45, 46, 47". In the latter case, after the statement "Input #1, A", I want A to equal "45, 46, 47" but all I actually get is "45". How do I get my VBA program to get all of "45, 46, 47" from a line in the file?
Is that what you're asking? If so, I think there's way to do it even using the native Basic commands, but I'll have to look it up. Is anyone else reading this who knows?

If no one answers, and I can't find it, then you can switch to using the FileSystemObject, which I know works. But first let's see whether you or I can find, or some lurker can tell us, the right form of the Input statement.
Reply With Quote
  #9  
Old 11-22-2013, 09:37 PM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Yes, that is it precisely! Thanks for explaining it better than I did! I'll do some google searching too.
Reply With Quote
  #10  
Old 11-23-2013, 08:42 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

Last night I was thinking about my old Basic programming, and I believe maybe I was wrong. It's coming back to me, vaguely, that on output statements (repeat "output"), a comma between arguments causes the data to be separated by a tab—either a literal <Tab> character or maybe just a specified number of spaces, I'm not sure—but that a semicolon writes out the data all jammed up against each other, leaving it up to the programmer to decide where to write spaces and so forth.

That's for output. For input, I've an even vaguer notion that commas are more of a problem, maybe an insoluble one, though I'm reluctant to believe it.

Unless someone comes along and straightens us out, I'm about to suggest that you drop the Open, Input and Close statements and use the FileSystemObject. For one thing, it gives you more options when checking drives, paths, dates and other such properties of files. For another—and this is probably the biggest reason—I know how it works .

If you decide you're willing to go that route, or at least to think about it, check out again the code I wrote below and look at Microsoft's on-line documentation here. The point is that the ReadLine method reads an entire line of data at once, leaving it to you to break it up into pieces, which in this case is just what you want.
Reply With Quote
  #11  
Old 11-23-2013, 10:25 AM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Thank you. I feel that I am so close! I will do the FSO method. However, I get an error "object not found" at

Code:
Set fo = fso.OpenTextFile(Fname, 1) 'ForReading
Fname is the full path of the text file. I get it through the dialog

Code:
Set f = Application.FileDialog(1)
f.AllowMultiSelect = False
f.Title = "Choose one or more Gaussian Output files"
'f.InitialFileName = ActiveWorkbook.Path
f.Filters.Clear
f.Filters.Add "Gaussian Output Files", "*.txt"
f.InitialView = msoFileDialogViewList
I also enabled the scripting library, so I'm pretty sure that's not my problem.

Thanks again!
Reply With Quote
  #12  
Old 11-23-2013, 11:03 AM
BobBridges's Avatar
BobBridges BobBridges is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 616
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

I'm going to ignore the FileDialog for now. You can incorporate it into your program—it's often very useful—but in order to keep things simple at first, let's assume for this discussion that you already know the full path and filename the file you're going to read. I'm guessing your problem is that you haven't yet defined the object that I called "fso"; that is, the statement "Set fo = fso.OpenTextFile(Fname, 1)" is correct enough, but when VBA gets that far it finds it doesn't know what fso is. It isn't built into VBA as the name of the FileSystemObject; you have to define it.

Depending on whether you want to define it in the References window (I think they call this "early binding"), there are two ways to do it. If you define it in the References—I think it's it more than one of the libraries that have "Scripting" in their titles—then you can just do this:
Code:
  Set fso = New FileSystemObject
Otherwise you have to use the CreateObject function:
Code:
Set fso = CreateObject("Scripting.FileSystemObject")
Either one works; the only difference (I think) is that "early binding" allows you to use the built-in enumerations, eg "ForReading" instead of 1. I do a lot of VBS, too, so I use either one pretty casually.

Once you've got the fso object defined—and you don't have to call it "fso", you can give it any variable name you choose—then your program should proceed normally. I think.
Reply With Quote
  #13  
Old 11-23-2013, 12:10 PM
omahadivision omahadivision is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Oct 2012
Posts: 28
omahadivision is on a distinguished road
Default

Thanks! It now does everything I want it to! I am very appreciative!
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 08:21 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft