Hi Henrik

I hope this isn't related to the problem I raised yesterday about Conditions (Like %=).

Basically, the Condition doesn't seem to be working correctly.

Here is the scenario:

I have a 'List' of values created from a READ FILE task, for example:
===============
FileMatch : DEM
Cust_Id : S0006
App Root : C:\@@TRIGGER\
Reslib : C:\@@TRIGGER\
Spool : C:\@@SPOOL\
DocDef : DEMDFA
DPrf : ppde
Config : mconf3
Set : mset3
Profile : testmprf2
===============

I then have a FOR EACH LOOP on Y = {TASK(450b63f9-1571-4a25-85c2-d6ac52086369,StdOut)}

I have also set the Delimiter to ':'

In the Loop. I am assigning a Variable '@filematch' to the value: {LOOP(CurrentValueXArray,1)} - The variable is also set to 'Transalte to constant when running' checked.

I have a Condition on the SET VARIABLE task :
Value 1 : {STRING(Trim|{LOOP(CurrentValueXArray,0)})}
Type : String
Condition : Equal (=)
Value 2 : FileMatch

The Condition Action is set to 'Dont Wait' and 'On Match All - Continue', 'On Match Any - Next'.

So basically, using the List above, I want to set the variable @filematch to the value 'DEM' (because the condition says to apply only when 'CurrentValueXArray,0' equals FileMatch (trimmed and case sensitive as it is in the List).

So as far as I can tell everything is set up correctly, however, the variable @filematch is being set to the last value in the list (CurrentValueXArray,1) which is 'testmprf2', however this is the 'Profile' value.

So what is going wrong here?

Cheers

Andy

I think the problem is translate to constant. If you use that it will keep value 1 through the whole loop.

---

Hi Henrik

When I uncheck translate to constant, then write the variable out a WRITE FILE task (Filematch = {USERVAR(@filematch)} ) I get the following:

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

Filematch = DEM
Filematch = S0006
Filematch = C
Filematch = C
Filematch = C
Filematch = DEMDFA
Filematch = ppde
Filematch = mconf3
Filematch = mset3
Filematch = testmprf2
====================

So it is creating @filematch as an array, where it should only contain the value 'DEM'.

The WRITE FILE is placed within the LOOP however if I place it outside the LOOP I am getting the following error: 'Filematch = [Current context (running Task) is not in a loop] '

Basically I need to call an external '.EXE' and pass the value 'DEM' as a parameter.
Cheers

Andy

Could you post the original file here?

---

Hi Henrik

The original lookup file? It is exactly as posted above, but the actual file is attached as well. Also, the original CSV file which the Powershell script reads and matches. I have also exported the particular Job, conditions and Variables if that would help any? Basically the workflow is:

1. Data file is dropped into the C:\@@Trigger folder (sample file name to use is 'DEM_110630_110630130039_00001.JRN' - content is not important at this stage). This triggers the Job.
2. Powershell script is run and matches the Trigger input file with an entry in the CSV Lookup file 'C:\@@Trigger\Profile\FileAcct.csv'
3. Powershell writes a lookup file to $outfile ("$currroot\$currfile.CSV") which is made up of values from VC Variables ($currfile = "{TRIGGER(Active,LastTrigger,File.Result.Name)}" and $currroot = "{USERVAR(@dfltroot)}") - sample file is 'DEM_110324_110324115311_00001.LOG.CSV' attached
4. CSV File is read and variables assigned (the bit which isn't working).

Hopefully this is enough for you? If you need anything else then please let me know

Cheers

Andy

Hi Henrik

Did you receive the files I posted in the new thread following this one?

Cheers

Andy

Thanks for the files. We will check them tomorrow. We merged the threads into this thread now.

---

One problem with the file is that the first row contains some descriptions of columns which do not match the format of the following rows.

Try removing the first line in the file.

---

Hi Henrik

The first file (Fileacct.csv) is a CSV file from Excel and it writes out the column headers, however, I can't see how those heading fields would make a difference? I am looping through the file looking for a name match in the first column - if it doesn't match it should skip it. The first row/column contains the text 'FileMatch'.

That said, this file is processed by a powershell script which produces a list :

FileMatch : DEM
Cust_Id : S0006
App Root : C:\@@TRIGGER\
Reslib : C:\@@TRIGGER\
Spool : C:\@@SPOOL\
DocDef : DEMDFA
DPrf : ppde
Config : mconf3
Set : mset3
Profile : testmprf2

I am trying to match the second column on this (e.g. DEM), and again I can't understand why that wouldn't work.

Cheers

Andy

It works like this;

Whenever you are using For each type in loop VisualCron is preparing a list of rows.

The problem is, that in your Task you are trying to access the second column. On the first row there is no second column, hence the error.

This *might* not be a problem for you.

But if I run your Job I get the following output:



If I change to {LOOP(CurrentValueXArray,0)} I get:



What is exactly wrong with this output and how would you expect it to be different - and why should it be different?

---

Hi Henrik

Okay - the Writefile (last task in the job) is writing out the following values:
Filematch = DEM
Filematch = S0006
Filematch = C
Filematch = C
Filematch = C
Filematch = DEMDFA
Filematch = ppde
Filematch = mconf3
Filematch = mset3
Filematch = mprf2

However, I am only writing out the 'Filematch' value just to test the logic is working. It should only output the first line 'Filematch = DEM' in this particular test.

The task 'Read csv Lookup file', is reading a file produced by the Powershell script. (Note: I am only using Powershell as there is currently no capability within VisualCron to parse, match and extract delimited fields in csv files or even in XLS files). This lookup file from Powershell is formatted as:

FileMatch : DEM
Cust_Id : S0006
App Root : C:\@@TRIGGER\
Reslib : C:\@@TRIGGER\
Spool : C:\@@SPOOL\
DocDef : DEMDFA
DPrf : ppde
Config : mconf3
Set : mset3
Profile : mprf2

in the 'set filematch variable' task, I have set a condition that is comparing Value 1 ({STRING(Trim|{LOOP(CurrentValueXArray,0)})} which is the first column of the powershell lookup file above) to be equal to Value 2 (FileMatch). If it is 'Filematch' then set the VC user variable @FileMatch to the subsequent Column 2 value (DEM)

Then in the next iteration of the loop, check to see if the first column is 'Cust_ID' - if it is, then set the VC user variable @custid to 'S0006', same for @docdef, and @reslib and all the other variables that I haven't yet defined.

Basically, in the above example, there are 10 records. I need 10 user variables each named as per the first column as VC user variables, with each containing the respective values from the second column.

Hopefully that makes sense?

Cheers

Andy

Perhaps I am misunderstanding the task.

I do not think it is the best option to use the Loop for parsing the rows (in the attached file). But perhaps you were referring to parsing this list instead?



Sorry for any misunderstanding but it is hard to see the full picture here. But - if you are going to parse the above list I see not problem but the blank spaces you are using between the "name" and the "value".

But were you talking about parsing the file attached here instead?

---

Hi Henrik

No, that is the list that I need to parse as you have defined in your last post. The other list was just a sample produced when using list out so we can forget that one.

Basically, I need each of the values in the right hand column assigned to 10 different variables that I can use as parameters when calling an external exe. So I will have 10 VC user variables (@filematch, @custid, @approot, @reslib, @spool, @dcodef, @dprf, @config, @set and @profile) and these will each hold the respective values from the second column (DEM, S0006,C:\@@TRIGGER\,C:\@@TRIGGER\,C:\@@SPOOL\,DEMDFA, ppde, mconf3,mset3 and testmprf.

It all stems from the need for me to read a lookup file containing, for example, 100 delimited records, and based on a triggered file name, find a matching record in the lookup file (first column) and when a match is found, allocated all the subsequent fields in that record to individual VC User variables that can then be used in subsequent tasks and jobs.

I can do this using many different tools and scripts but I want to keep all processes within VC for auditing, logging and accounting purposes.

Cheers

Andy

Ok, so what we currently not do in this version is to translate the Variable name. That is causing the problems.

We managed to produce this list when adding that support:



As you can see there are some problems with some values like @AppRoot. The reason is that you use a colon in your PowerShell output and there is a colon in the value. I suggest using a = in the PowerShell output. We will build a new version so for you to test.

---

Hi Henrik

That would be absolutely perfect if you could add that support! It would give us the functionality we are looking for. I realise the issue with the drive name (C:) so I will change this (only because I don't know how to change from a ':' to an '=' in powershell.....

Thanks again

Andy

Ok, download this version http://neteject.com/down...Cron/VisualCron6.0.4.exe 

Uninstall current version first.

I am attaching our test Job.

---

Hi Henrik

I imported the testjob.zip but I can't see the job you tested with.... I can only see an MG_TEST group containing a job named MG_TEST_IMPORT. There is nothing obvious in there that shows how files are read?

Cheers

Andy

Ok, something went wrong with the export. Basically, we have the following:

1. 1 read Task, just reading your sample file
2. 1 set Variable Task:

Name property: @{LOOP(CurrentValueXArray,0)}
Value property: {LOOP(CurrentValueXArray,1)}

3. 1 write file Task
Value property: @{LOOP(CurrentValueXArray,0)} = {USERVAR(@{LOOP(CurrentValueXArray,0)})}

---

Here is the file again.

---

Hi Henrik

To update you - this now works very well - thank you very much for your efforts to resolve the issues with conditions and automatic assigning of Variables from an external lookup file. I have tested this in our 'real' environment and it works very well.

However (there is always a 'But' 🙂 ). I still depend on running an external process (Powershell script) in order to pre-process a CSV file to find a matching row, and then present a re-written file in to the 'Name/Value' Pairs that is now possible within VC (as per the above). I think it would be a very useful feature to handle CSV files natively within VC, i.e. to parse and find matching rows, and automatically assign all of that row's columns into VC variables.

Would be very useful for accounting and auditing purposes, e.g. writing data generated elsewhere into an SQL database?

Thanks again for all of your support

Andy