[RESOLVED] Parsing from text file to mainfraim application

[Bill-E-BoB]

Hi guys, just found this forum this morning & have spent the whole day reading through the wealth of knowledge you've all created here. I was hoping somebody might be able to help me with a project I'm working on...

I need to write a program that will pull data from a text file & put it into a mainframe client session. In the past, everything similar that I've written pulls from excel, which (to me anyway) is nice & simple. I just define my spreadsheet as an object, define my cell as an object, Define X as my start row to end row on the spreadsheet and then use codes like this:

TEXT = objWorkBook.Sheet1.Cells(X, 4).Value
Sess0.Screen.Sendkeys (TEXT)

That works great when my raw data comes from excel, but my current project is to write a similar macro that parses a text file instead of a spreadsheet. Below is an example of the text I need to pull from:

INTERNET CENTER:
COMPANY NAME: Fakecompany Inc
STREET ADDRESS 1: 123 Main St.
STREET ADDRESS 2:
PROV/MUNI:
CITY, STATE, ZIP: Amityville, VA, 20151-

I know there is a way for me to have it look for the "COMPANY NAME:" label & then set the rest of that line as a variable for me to send to my session...but I have no clue how to do it. How do I make the text file an object? How do I get the code to pull the right data out of the text? After reading through about 8,000,000 posts on here and all the helpfiles I can find...about all I've managed to do is get my code to open the text file in notepad by using this line:
'defines "ORD" as the order # typed into the dialog box that begins the program.
ORD = dmain.textbox1
'opens the requested order
Shell "notepad.exe " & "C:\my folder\" + ORD + ".txt"

Isn't there a code that lets me just basically do the following (this isn't real code, this is just what I want to do)
COMP_NAME = (text after "COMPANY NAME:" until end of row)
Sess0.Screen.Sendkeys(COMP_NAME"<ENTER>")

[krckoorascic]

try something like this:

Dim ff As Long
Dim line As String
Dim compName as String
 
ff = FreeFile
Open "C:\data.txt" For Input As #ff
  While Not EOF(ff)
    Line Input #ff, line
    select case True
        Case Ucase$(Left$(line,13)) = "COMPANY NAME:"
           compName = Mid$(line, 14)
           Debug.Print "Company name: " & compName
    End Select
  Wend
Close #ff

[Bill-E-BoB]

Sweet, that works! As I have more time I'll have to figure out WHY it works so that I can replicate it for each piece of information I need to upload...so I'll probably have more questions. The Debug.Print command didn't work, but I just put Sess0.Screen.Sendkeys(compName) after it and it appeared to work.

Just a couple quick questions (I'm late for my other job so I should really be leaving right now...) but I'm excited about this, so I've gotta make sure I'm understanding...
Case Ucase$(Left$(line,13)) = "COMPANY NAME:" is checking to see if the first 13 characters from the left of the row say "COMPANY NAME:", right? and then if it is...we're defining compName as the characters from the 14th character to the end of the row?

[krckoorascic]

...The Debug.Print command didn't work,...

Debug.Print just outputs to Immediate window so you can see whats going on...

Just a couple quick questions (I'm late for my other job so I should really be leaving right now...) but I'm excited about this, so I've gotta make sure I'm understanding...
Case Ucase$(Left$(line,13)) = "COMPANY NAME:" is checking to see if the first 13 characters from the left of the row say "COMPANY NAME:", right? and then if it is...we're defining compName as the characters from the 14th character to the end of the row?

yeap you're right, just do the same for other values

[Bill-E-BoB]

Awsome, thanks for the help!!

[Bill-E-BoB]

Can I bug you for one more thing? I ran into a new problem you may be able to help me with... There's one section of the order that doesn't follow the same labeling convention...the pricing line items don't have a label that I can search for in the string. An example is below, can you help me figure out what to do with it?

============================================================================
PRODUCT/SERVICE INFORMATION
============================================================================
GROUP NAME: ??
LINE ITEMS -
QTY PIC TARIFF TERM DESCRIPTION INST($) EACH($) L/P M/N Type
1 SL0005 1894 3Y LINE ITEM .00 .00 N M ACCE
1 SL0006 1895 3Y LINE ITEM .00 .00 N M ACCE
1 SL0007 1896 3Y LINE ITEM .00 .00 N M ACCE
1 ST2042 37276 3Y LINE ITEM .00 .00 N M PORT

============================================================================

Basically, I need to identify the row the starts out with "QTY PIC TARIFF" and then copy the code under the tariff column and the value under the QTY until it hits the blank line at the end of the table (tariff1 = 1894, tariff2 = 1895, etc. QTY1 = 1, QTY2 = 1, QTY3 = 1). There could be any number of line items, it won't always be 4...so I need the program to keep creating new objects until it hits the blank line at the end. Any ideas? Thanks!!!!

[Al42]

Continuing on the previous theme:

select case True
        Case Ucase$(Left$(line,13)) = "COMPANY NAME:"
           compName = Mid$(line, 14)
           Sess0.Screen.Sendkeys(compName)
         Case Ucase$(Left$(line,7)) = "QTY PIC"
           Line Input #ff, line 'input the next line
           Do While line > "" 'keep doing it until a blank line
             Sess0.Screen.Sendkeys(Mid$(line,10,4)) 'send the 4 characters starting at the 10th position
             Line Input #ff, line
            Loop 'back to the 'Do' line
    End Select

(This is off the top of my head without actually running it in VB, so there might be an error or two.)

[Bill-E-BoB]

Hmm...I think I'm doing something wrong still. If I use the code exactly like Al42 provided, it compiles & runs fine, but only looks at the first line & then moves on to the code that pulls out my tagged information (company name, etc.). So, I put a When statement around it similar to the code I was using from krckoorascic so that it would stop trying at the end of the file...but now it hits an infinite loop and I can't figure out why. I had to modify the code to fit my application...so maybe I screwed something up in the process? Unless I'm crazy, this should work...unfortunately, I think I'm crazy.

While Not EOF(ff)
    select case True
        Case Ucase$(Left$(row,13)) = "COMPANY NAME:"
           compName = Mid$(row, 14)
           OESOTS.Screen.Sendkeys(compName)
         Case Ucase$(Left$(row,7)) = "QTY PIC"
           Line Input #ff, row 'input the next line
           Do While row <> "" 'keep doing it until a blank line
             OESOTS.Screen.Sendkeys(Mid$(row,12,5)) 'send the 4 characters starting at the 10th position
             Line Input #ff, row
            Loop 'back to the 'Do' row
    End Select
  Wend

By the way...the changes that might not make sense without seeing my whole code...I had to dim row as string instead of line like you guys had been using...not sure why, but it didn't like having a string named "line"...and the other change is I'm using 2 different sessions, so I left one with the standard sess0 name, but the one that this data goes to is named oesots now instead of sess0.

[Bill-E-BoB]

I'm still stumped. That code Al42 gave me looks good...I swear it should be working. It just doesn't like me. Anyone have any ideas?

[Bill-E-BoB]

Alright, I got it to work now. Pretty close to your suggestions, thanks a million for the help you guys, I probably never would've figured this stuff out.

If you're curious, here's what worked:

While Not EOF(ff) 'stops when its at the end of the file
    Line Input #ff, row ' checks next row
        select case True
        Case Ucase$(Left$(row,7)) = "QTY PIC" ' checking for pricing section
           While row <> "" 'repeats until it hits the blank row after pricing
           Line Input #ff, row 'hits the next row
           TARIFF = Mid$(row, 12, 5) 'gets the tariff code
           NB_QTY = Left$(row, 2) ' gets the quantity
           OESOTS.SCREEN.SENDKEYS(TARIFF + "<ENTER>") 'puts it in the session
           OESOTS.SCREEN.SENDKEYS(NB_QTY + "<ENTER>")' puts it in the session
           Wend
        End Select
 Wend