Stripping Data From Long Variable To String

[PaulSR]

Hi,

OK so I'm trying to strip down a lengthy variable and break out the parts into an array. I'm starting to think I'm going to need some VBScript but thought it worth asking first if there's a better/leaner answer - I'm guessing somebody has already done this at some point?

So I extract this data from the application and I've added prefixes (at the application end) for each data item meaning I end up with this held in the clipboard :

THIS IS THE TABLE TEST
EFF_DATE: This is a test for Effective date TRANCODE: This is a test for Transaction code DESCN: This is a test for Description AMOUNT: This is a test for Amount
EFF_DATE: This is still a test for Effective date TRANCODE: This is still a test for Transaction code DESCN: This is still a test for Description AMOUNT: This is still a test for Amount
EFF_DATE: Yet another test for Effective date TRANCODE: Yet another test for Transaction code DESCN: Yet another test for Description AMOUNT: Yet another test for Amount
EFF_DATE: Getting bored yet? TRANCODE: Getting bored yet? DESCN: Getting bored yet? AMOUNT: Getting bored yet?
EFF_DATE: I am TRANCODE: I am DESCN: I am AMOUNT: I am

So basically I want to loop around this data grabbing each item and putting it in the array - Is there an easy way to go about that? I'm mucking about with separate etc but it's not coming together.

[JRL]

Can we start at the beginning? How are you extracting the data from the application? Perhaps you can use ODBC and import the data directly using a connection string and Macro Scheduler's database connection functions.

If we can't start at the beginning.
If you can add "This is a test for..." you could instead add commas then you would have a csv file format. From there you could use CSVFileToArray>.

[PaulSR]

I'm extracting this data from a citrix receiver window so there are no ODBC connections or anything of the sort available which has proven to be extremely painful.

So, what I've done is set up a diary/letter within the application which I then output the data I need on - this subsequently allows me to highlight and copy the data which in turn means I have the data in the clipboard.

Should be able to get it into a comma-delimited format without too much hassle - stupid question of the day I guess I'll need to output this data somewhere, save as a csv file then import back into MS? No way to push it straight from clipboard to array?

[Marcus Tettmar]

So you are using GetClipBoard to get the data?

you could then use Regex to get the bits. Trouble is you only have prefixes, so I'm not sure how we stop extracting unless we use the next prefix. Can you also add suffixes? Then it's just a simple expression to get all between the prefix and suffix and put each match into an array for each set of prefix/suffix.

[JRL]

If you can get it to a CSV format in the clipboard you might try something like this.

Code: Select all

Let>comma=,
GetClipboard>vData

Separate>vData,crlf,Line

Let>kk=0
Repeat>kk
  Add>kk,1
  Let>value=line_%kk%
  Separate>value,comma,Item
  Let>ii=0
  Repeat>ii
    Add>ii,1
    Let>Item_%kk%_%ii%=Item_%ii%
  Until>ii=Item_count
Until>kk=line_count

[PaulSR]

Yeah I was using getclipboard and just chucking it in a modal to make sure it was working but now I need to store it in a useable format.

I should be able to bookmark the text at both ends if that'd work?

[Eff_date]this is the date[/Eff_Date] type thing?

[PaulSR]

Thanks a lot guys - JLR I'm having a play with your solution at the moment I think that might be within the realms of possibility (for me).

[hoangvo81]

using marcus idea of adding a suffix.. by using a prefix of the next as teh sufix of the previous.

Code: Select all

let>test=THIS IS THE TABLE TEST EFF_DATE: This is a test for Effective date TRANCODE: This is a test for Transaction code DESCN: This is a test for Description AMOUNT: This is a test for Amount EFF_DATE: This is still a test for Effective date TRANCODE: This is still a test for Transaction code DESCN: This is still a test for Description AMOUNT: This is still a test for Amount EFF_DATE: Yet another test for Effective date TRANCODE: Yet another test for Transaction code DESCN: Yet another test for Description AMOUNT: Yet another test for Amount EFF_DATE: Getting bored yet? TRANCODE: Getting bored yet? DESCN: Getting bored yet? AMOUNT: Getting bored yet? EFF_DATE: I am TRANCODE: I am DESCN: I am AMOUNT: I am
let>comp_1=EFF_DATE:
let>comp_2=TRANCODE:
let>comp_3=DESCN:
let>comp_4=AMOUNT:
let>count=1
let>start=1
let>continue=1
while>continue>0
    let>k=0
    let>j=0
    repeat>k
        add>k,1
        let>j=k+1
        let>subValue=comp_%k%
        pos>%subValue%,test,%start%,posR,FALSE
        if>posR>0
            'found, go to the next comp
            if>k=4
                let>j=1
            endif
            let>start=posR
            let>subNext=comp_%J%
            pos>%subNext%,test,%start%,posN
            if>posN>0
                'found
                'pull all up to 
                sub>posN,1
            else
                'last one cannot find the next code
                'set to exit repeat
                let>k=4
                'grab all teh rest
                len>test,total
                let>posN=total-start
                add>posN,1
                'set to exit while
                let>continue=0
            endif
            midstr>test,%start%,%posn%,temp
'if you  dont want the prefix
           stringreplace>temp,%subValue%,,temp
           trim>temp,temp
            let>thisArray_%count%=%temp%
            add>count,1
        else
            let>continue=0
        endif
        
    Until>k=4
endwhile

[Marcus Tettmar]

But with RegEx you could get all the parts into arrays in a lot less lines. One line per section. You would use this pattern type:
https://www.mjtnet.com/blog/2012/10/29/ ... sed-regex/

[hoangvo81]

I looked at your regular expression and its simplified the item.. quite a alot compare to what i had done.

Code: Select all

let>test=THIS IS THE TEST TABLE EFF_DATE: This is a test for Effective date TRANCODE: This is a test for Transaction code DESCN: This is a test for Description AMOUNT: This is a test for Amount EFF_DATE: This is still a test for Effective date TRANCODE: This is still a test for Transaction code DESCN: This is still a test for Description AMOUNT: This is still a test for Amount EFF_DATE: Yet another test for Effective date TRANCODE: Yet another test for Transaction code DESCN: Yet another test for Description AMOUNT: Yet another test for Amount EFF_DATE: Getting bored yet? TRANCODE: Getting bored yet? DESCN: Getting bored yet? AMOUNT: Getting bored yet? EFF_DATE: I am TRANCODE: I am DESCN: I am AMOUNT: I am
STRINGREPLACE>TEST,THIS IS THE TEST TABLE,,test
let>pattern1=(?<=EFF_DATE:).*?(?=TRANCODE:)
let>pattern2=(?<=TRANCODE:).*?(?=DESCN:)
let>pattern3=(?<=DESCN:).*?(?=AMOUNT:)
'this will only grab 4 of the 5, as the last one does not end in eff_date
let>pattern4=(?<=AMOUNT:).*?(?=EFF_DATE:)
let>rp=
regex>pattern1,test,0,EffDate,nEffDate,1,rp,test
regex>pattern2,test,0,TranCode,nTranCode,1,rp,test
regex>pattern3,test,0,Descn,nDescn,1,rp,test
regex>pattern4,test,0,Amount,nAamount,1,rp,test
stringreplace>test,EFF_DATE:TRANCODE:DESCN:AMOUNT:,,test
trim>test,test
arraycount>Amount,a
add>a,1
let>amount_%a%=test

basically just added 2 string replace function to remove the prefix: THIS IS THE TEST TABLE
and a second to remove all the tags left over by regex's find and replace.
and added the last entry for amount into the 'amount's array variable.

i don't know if this is consider clean, but it does reduce alot of the codes earlier.

EDIT:
sorry i had to disable HTML in the post.

[PaulSR]

Thanks everybody - based on hoangvo81's proposed script I've amended the generated doc from the application and in tandem with the following it seems to work exactly as I need.

let>test=THIS IS THE TEST TABLE sEFF_DATE This is a test for Effective date eEFF_DATE sTRANCODE This is a test for Transaction code eTRANCODE sDESCN This is a test for Description eDESCN sAMOUNTThis is a test for amount eAMOUNT sEFF_DATE This is still a test for Effective date eEFF_DATE sTRANCODE: This is still a test for Transaction code eTRANCODE sDESCNThis is still a test for Description eDESCN sAMOUNT This is still a test for amount eAMOUNT sEFF_DATE Yet another test for Effective date eEFF_DATE sTRANCODE Yet another test for Transaction code eTRANCODE sDESCN Yet another test for Description eDESCN sAMOUNT Yet another test for amount eAMOUNT sEFF_DATE Getting bored yet? eEFF_DATE sTRANCODEGetting bored yet? eTRANCODE sDESCN Getting bored yet? eDESCN sAMOUNT Getting bored yet? eAMOUNT sEFF_DATE I am eEFF_DATE sTRANCODEI am eTRANCODE sDESCN I am eDESCN sAMOUNT I am eAMOUNT
STRINGREPLACE>TEST,THIS IS THE TEST TABLE,,test
let>pattern1=(?pattern2=(?pattern3=(?pattern4=(?rp=
regex>pattern1,test,0,EffDate,nEffDate,1,rp,test
regex>pattern2,test,0,TranCode,nTranCode,1,rp,test
regex>pattern3,test,0,Descn,nDescn,1,rp,test
regex>pattern4,test,0,Amount,nAamount,1,rp,test
stringreplace>test,EFF_DATE:TRANCODE:DESCN:AMOUNT:,,test
trim>test,test
arraycount>Amount,a
add>a,1
let>amount_%a%=test
let>ClaimsCount=nEffDate
//Display The Data
Let>Counter=0
Let>Output=

Repeat>Counter
Add>Counter,1
Concat>Output,This is Array %Counter% :
Concat>Output,EffDate_%Counter%
Concat>Output,Trancode_%Counter%
Concat>Output,Descn_%Counter%
Concat>Output,Amount_%Counter%
Concat>Output,CRLF
Until>Counter,nEffDate

MessageModal>Output
MessageModal>%ClaimsCount% Claims Were Extracted From Diary Data.

Cheers for all the advice.