Parsing Excel Test Script with Reference Keyword List

Hello Everyone,

This is my first post.

I need some assistance with parsing test steps in an excel document. The cells will be altered later by a software program, so I need the parsing done in VBA and cannot place formulas into cells to accomplish my goal.

I have done some of the find/replace work in my existing file, however there are quite a few issues left that I'm not sure how to handle.

Starting out, all of the test steps are in their own rows and on a single column. They are full sentences with keywords that I need to cut and paste to a column to the left of the steps in an intelligent order.

Here is what I currently have:
Keyword Post File.xlsm

Working with one step (single line or row) at a time, I need to pull out command keywords that are listed in the keyword sheet and place them in the left column in the order that the step describes. They must have their own row.
Next, I need to move all of the I/O keywords from the step to their own row but stay in the current column. They will be adjacent to their corresponding command keyword. I will also move the I/O keyword's value and units (if it has any) to the column next to it.
Finally, I need to remove any words that are not command keywords, I/O keywords, values, or units.

I'm terribly sorry that this is so long but it is rather complex. I have provided a sheet in the linked document as to what I hope to achieve, as well as, the keyword list and the steps. The VBA code will show what I have up until now. The document should clear up what I'm trying to do. Any help will be appreciated!

Thanks in advance!

gmk,

Thanks for your reply!

I stand to be corrected but as defined there are 252 possible states of equipment ignoring Meter 1 readings and the wait periods of seconds and minutes.

The script that I provided as an example for my situation, contains only 1/10 or less of the actual I/O keywords. So the number of states, is quite a bit higher than 252.

Is it safe to assume each LED can only controlled by its corresponding numbered switch?
What is 'on' synonymous with? Is it 'up' or 'down'? And the reverse state of 'off'? Or must all be kept discreet?

The effect of the position of any switch is variable and isn't reliable enough to pair with a single I/O keyword.

I would only be verifying LEDs(Binary Out (BO)) and meters(Analog Out (AO)), setting switch(Binary Input(BI)) positions and temperatures(Analog In(AI)). So I could pair "Verify" with any BOs or AOs, and "Set" with any BIs or AIs.
So I guess all I really need now is help with parsing the keywords and any words I think may be needed. Like parsing "Zone Temp to 78F" and "LED1 is on" from "Set the Zone Temp to 78F and verify LED1 is on."
If I can work left to right on each line with the hope that the sentences follow the format of "Command...I/O...Value", most of the steps will be ok.
The order could be done manually after the keywords are parsed.

What could be used in pulling a keyword and its supporting text?

Using what I have now, how can I take : " Verify LED 1 is on and set swtich 1 up." and parse "Verify LED 1 is on" and "Set swtich 1 up"?

Thank you!

cMac333i

"Command...I/O...Value",

In the examples provided it seems it is true to say that an instruction is always terminated by either 'Unit' or 'I/O"? Do you agree?

gmk,

It would be terminated by a unit or value. Units being seconds, minutes, F, %, etc. or Value being On, Off, Up, Down, etc.

cMac333i

This is a trial to see how closely it gets to your target. At the moment it does not create another row for multiple instructions in a script.

Put the list of sample scripts into a new wbook, wsheet1, cell "B2"

Please Login or Register  to view this content.

Put the code into a standard module and run.

For the examples you provided it will parse the instruction as you required unless it is a multiple instruction. I have created a column showing the remainder of the script after parsing the first instruction. This will demo the difficulties you may get with further parsing.

Either develop the code yourself or get back if you have any difficulties.

hth

gmk

Please Login or Register  to view this content.

gmk,

Thanks! That does look pretty close to what I need. Keeping the remainder will help to generate some code to handle the multiple command steps. I think with a little more work, converting the thousands of scripts that we have won't be as difficult of a task when it gets to the manual cleanup and review.

One last question...

Please Login or Register  to view this content.

How can I set this array to reference a column on another sheet? I have MANY more I/O points to add and putting them all in the code may not be the best way. Also, the keywords I provided are just generic names to make things easier. I will be using keywords that have FAR more possible ways of being written. What is the best way to handle each list of keywords?

Thanks again!

cMac333i

That does look pretty close to what I need

Good. :) I didn't see much point progressing until you had tested with more real life scripts because the present code is the core method.

Yes, 'Remainder' is there for looping purposes for each script as you have picked up. The notion is to count the number of 'command' words in each script then loop the code, inserting an entire row for each loop, until they are exhausted.
A reminder though.... it will not generate the extra steps required for scripts such as 'Set s 3 up and verify that nothing changes.' And on that point can you confirm that script would generate this?

Please Login or Register  to view this content.

For now, I think we are some way off that as we have to get the basis right first. We can hopefully get 'smarter' as we move on.

How can I set this array to reference a column on another sheet?

That is easy enough. We can either use 'dictionaries' or arrays. The arrays are 'hard coded' at the moment simply through lack of further information. If you have lists of any of the keywords held elsewhere the code will be able to build a new array by referencing each list. What becomes necessary then is to 'hard code' where the lists are held.

I have MANY more I/O points to add and putting them all in the code may not be the best way. Also, the keywords I provided are just generic names to make things easier. I will be using keywords that have FAR more possible ways of being written. What is the best way to handle each list of keywords?

Continuing on from the last point on arrays.... it's clear the longer the list of each key word the longer the code will take to complete. Just how long the lists are only you are aware and I suspect they may well be edited from time to time anyway. You can either have one long list of each type of key word or break them down into smaller groups dependent on say the type of job or activity. You may want to give some thought to that aspect. To counter that idea the code does move through the list pretty quickly but I've only got short script lists. Perhaps you should evaluate longer lists with the existing keys.

I'm pretty sure you will continue to test against your library of scripts and if anomalies occur which you think will impact on the core code let me know.

The problem with code of this type is it can never better the human brain in interpretive skills. I'm being a bit cautious because of the numerous ways an 'instruction' or script can be written and you could finish up with nonsense like.....Set On 100% Switch 1. And I am assuming that is the reason for this...

when it gets to the manual cleanup and review...

Do you have decimals in scripts?

Finally.... what is your time frame on this? I will do what I can but there are many other things to fill my day. :)

Gmk,

A reminder though.... it will not generate the extra steps required for scripts such as 'Set s 3 up and verify that nothing changes.' And on that point can you confirm that script would generate this?

Please Login or Register  to view this content.

Im not sure what you mean by this. I think I may have addressed this lower in this post. Sorry!
But I'm thinking that a good way to handle a step that contains, "Verify nothing changes", is to look in the new script for each Command keyword and copy each line from the bottom up. If a line is copied that has the same Control keyword as a previous copy, then skip to the next step up and finishing checking the rest. For example:

(I altered it a little to better describe my idea.)

Please Login or Register  to view this content.

The steps added would be:

Please Login or Register  to view this content.

Before I altered the script above to add in the steps that were not generated from the remaining script, I came across an issue. This may be to your point that I'm confused on. If you do not fully parse each step before a "No change" step, you could be looking for the wrong values in the final script. So, we will need to parse the remaining script of each step before moving to a new step.

You can either have one long list of each type of key word or break them down into smaller groups dependent on say the type of job or activity. You may want to give some thought to that aspect.

Most scripts will use about 80% of the final list of keywords so I could create seperate lists for each type of test and cut out 20% of the list.

The problem with code of this type is it can never better the human brain in interpretive skills. I'm being a bit cautious because of the numerous ways an 'instruction' or script can be written and you could finish up with nonsense like.....Set On 100% Switch 1. And I am assuming that is the reason for this...

That is correct. Sometimes its hard enough just to understand the written scripts. I can only imagine the difficulty a script generator could have.

Do you have decimals in scripts?

As in: 73.8 degrees F? Yes and all decimal numbers in the scripts can be cut down to one decimal place, IF any are of higher precision.

Finally.... what is your time frame on this?

Well I hope this isnt taking up too much of your time. I may need something about 80-90% final in a couple of months.
A time frame has not really been set. But I appreciate any help, when you can!

cMac333i

Please check your PMs for scoping type queries.

Thanks.

gmk

cMac333i

Without answers to queries contained in the Private Message I am not able to progress much more now.

In the interim I have moved on and the developed code now handles multi action scripts, rounds decimals to 1 significant place, enters 'suggestions' for missing command words and more, but is not yet fully recursive. Scripts such as 'Set s 3 up and verify that nothing changes.' are resolved to...

Please Login or Register  to view this content.

... leaving 'that nothing changes' as a remainder. I note your suggestion for handling such.

It would be interesting to progress this should you wish to.

gmk

cMac333i

Thank you for the information. In the meantime attached is the developmental code so far. It will maybe give you some ideas regarding the final manual check which we can discuss at a later stage.

Please note comments on how to use in Col "R"

Have fun.

gmk

Keyword Post File 1.2.xls

Here is the file that I encountered an issue with.
Thanks!

cMac333i

When adding the new control word 'compressor' to the arrays on wsheet 3 you have inadvertently typed 'compressor<space>'. Remove that and it should be good to go again.
In the new version I'm writing to include temperatures, I will also protect against such typos by 'Trimming' all listed key words before creating the arrays.

gmk

cMac333i

In the attached wb is some new code which should get you a little bit closer to the target. There is still some way to go but the essential components are in place. It's now a matter of evaluating against all the scripts you have.

Let me know how you get on.

gmk

KeyWord WorkShop.xls

cMac3331

Attached is v1.4.
This handles sentences with 2 command words but no control or unit word between eg 'Verify there is no change then set switch 3 down.'

Attachment 164002

gmk

v1.4_Issues.xlsx

Here is the list I mentioned in my message.
Thanks!

cMac333i

Attached is v1.6. This solves the use of multiple control words and one or two other matters. This is still developmental so I have added some test result structure which may help in your own tests.

Let's see how far this gets.

KeyWord Workshop 1.6.xls



gmk

@cMac333i

Attached is v1.9. This deals with control word abbreviations and other minor 'tidying up'. It optimises code and you should see a reduction in processing time of greater than 20%.

KeyWord Workshop 1.9.xls


gmk

@cMac333i

Attached is v1.9.2 for testing.

KeyWord Workshop 1.9.2.xls



gmk

@cMac333i

Attached is v1.9.3 for testing.


KeyWord Workshop 1.9.3.xls

@cMac333i

Attached is v1.9.4 for testing.

This permits 's' as both a control AND unit word; also recognises '##F' as a unit.

KeyWord Workshop 1.9.4.xls


gmk

@cMac3331

I'm getting: "68 script(s) processed in 0.12 seconds." on average.

Not that it matters a great deal with this work but that's an interesting result. This afternoon I ran 64 lines on old code from the start of the development and got around 1.45 seconds. But with the latest v1.9.4 I now get...

Timer.jpg

I expected to see an improvement in your times as well but they have hardly varied from the outset.

gmk

@cMac333i

This demonstrates how to deal with infinite loops which may occur if different grammatical styles arise. You might choose to read up on Regex Expressions, not perhaps the easiest subject to get into it is a very useful and flexible method which lends itself to parsing.

KeyWord Workshop 1.9.9.xls

Good luck with your project.

hth

gmk




Please remember to click the * below the post to say thanks and mark the thread as solved if answered satisfactorily.