Parse Text

[jsares]

I need to parse several fields from a screen dump. The word count changes so MiddleWords doesn't always work.

Here's some examples:

 CBEFDKR              Work Order Display       Page 1 of 2      Site ID    001 

Account Number 619832-19 @CM Cust Info

MARIE EALY Status ACTV 1/04/02 Maj Min

---

 CBEFDKR              Work Order Display       Page 1 of 2      Site ID :   001 

Account Number 631748-01 @CM Cust Info

ANTHONY J BIONCI Status ACTV 12/28/01 Maj Min

---

I need to parse the account number, first, and last name. Somethings stay consistant like Cust Info. I think I need to use the Position function but I can't seem to figure it out.

Jason

[LiveOak]

We need a little more information. Do you know the column positions for each of the text elements. For instance, the starting column of the account number and the maximum number of characters. Not absolutely necessary, but it will make the problem much easier. Same info for the name. Other information could help. Are there always two spaces after "Account Number" and before the number. How long can the number be. Example of the text to be parsed are interesting, but what is really needed is the specification for ALL possible outputs.

-bd

[jsares]

I'm not sure what you mean by columns. The data is just a text screen capture from an 5250 terminal session. The account number is always xxxxx-xx or xxxxxx-xx. The name can be of almost any length and any number of words.

Here's another example with the parts that change in brakets:

 CBEFDKR                       Work Order Display               Site ID :   001 

Acct Nbr [874071-01] @CM Cust Info

[JERRY KEHOE USED CARS] Co/Dv/Frn [10 85 881] Maj Min

---

[kennedy]

The info you need to provide is the nature of the "whitespace". Is that whitespace all just spaces? Are their linefeeds at the end of each line? Are there tabs between each column? Is there ever more than one space together except between columns?

By understanding your whitespace we can better understand the easiest thing to leverage to compute what you're looking for.

Assuming there are linefeeds and/or carriage returns at the end of each line, you can easily find the name and account number. You can also find the account number by leveraging the constant "Acct Nbr" text.

Using your latter example as the template...

AcctNbr = LeftWords(Middle(Input, Position(Input, "Acct Nbr", 1)+9, 20), 1)

Name = Trim(Middle(Input, Position(Input, "Cust Info", 1)+10, 50), Position(Input, "Co/Dv/Frn", 1) - Position(Input, "Cust Info", 1)+10, 50)))

Well, that's the general idea. HTH.

Last

[jsares]

There are no tabs just spaces and linefeeds. As for more than one space between columns I doesn't appear so. I will attach a screen grab showing invisible characters.

I tried your calculations but with the Acct Nbr I keep getting the error "There are too few separators in this function." And with the Name I get "There are too many separators in this function." Any ideas?

Thanks,

Jason

PS I got the Acct Nbr calculation to work by:

Trim ( Left ( Middle ( CBIS , Position ( CBIS , "Acct Nbr" , 1 , 1 ) + 10 , 20 ) ,  10 ) )

---

[LiveOak]

Most of these types of data dumps from large systems follow fairly strict rules about what position on a line each data item begins and how long it can be. You may or may not be able to get this information, but if you can, it is BY FAR the easiest way to reliably pull out the data you wish.

-bd

[jsares]

LiveOak:

I wanted to thank you for your comment. It suddenly dawned on me that I was trying to hard to get the data. By using the position I have been able to get 90% of the data just by using Middle. In a few cases (address, comments) I had to collect several lines and use Trim and Right/LeftWords.

It's funny how we can make a problem much more difficult for ourselves when we get stuck looking at it from one perspective. I'm pretty much done with the new database now. Just a few more problems to work out.

Again thanks a million.

Jason