Hi Henrik (and all)

Now we have Loop support which is a big bonus for us, I am visiting my customer this week to implement VC across their site having now bought a site licence.

Now I am looking for some advice on the first step of the implementation plan :-)

One of the areas we need to automate is batch job processing. So an incoming data file sent via FTP needs to be run against a document composition tool, however, there are other parameters that need to be passed in order to do this successfully.

We need to read an external lookup table (CSV or XLS) and match a specific record with the data file (the first column of record 'x' of the CSV file is 'like' the filename). Once we do this, we need to extract all of the fields in that record individually and use them in subsequent tasks in the job.

A VC Tasks stores it's output in STDOUT, but I need to break this down in to individual 'Variables' that can be reused within the VC Job.

As far as I can tell, there is no easy way of doing this in VC, so I am suspecting that I need to employ some external process in order to do this, unless anyone can advise on an effective way of achieving this entirely within VC?

The nearest I have got is running a Powershell Script that does the matching and assigning of fields into variables e.g.:
============
$currpath = "{TRIGGER(Active,LastTrigger,File.Result.TriggerFolder)}"
$currfile = "{TRIGGER(Active,LastTrigger,File.Result.Name)}"
import-Csv $matchfile | Where-Object {$currfile -match $_.FileMatch} | Select-Object -First 1 | Out-File $outfile | %{$fm = $_.FileMatch; $cID = $_.Cust_id; $appRoot = $_.'App Root'; $DDef = $_.DocDef;}
=============

This works extremely well, however there is no way of returning the individual values to VC (e.g $cID, $appRoot and $DDef in the script above) so they can be used like usual VC Variables elsewhere in the Job. Could this be an easy feature to introduce to allow me to keep all automation functionality within VC?

Cheers

Andy

Hi all

Just wondered if anyone was able to provide any feedback on my post (#53) above? Sorry to push it but looking for a solution to parsing files pretty urgently

Cheers
Andy

Hi Henrik (and all)

Now we have Loop support which is a big bonus for us, I am visiting my customer this week to implement VC across their site having now bought a site licence.

Now I am looking for some advice on the first step of the implementation plan :-)

One of the areas we need to automate is batch job processing. So an incoming data file sent via FTP needs to be run against a document composition tool, however, there are other parameters that need to be passed in order to do this successfully.

We need to read an external lookup table (CSV or XLS) and match a specific record with the data file (the first column of record 'x' of the CSV file is 'like' the filename). Once we do this, we need to extract all of the fields in that record individually and use them in subsequent tasks in the job.

A VC Tasks stores it's output in STDOUT, but I need to break this down in to individual 'Variables' that can be reused within the VC Job.

As far as I can tell, there is no easy way of doing this in VC, so I am suspecting that I need to employ some external process in order to do this, unless anyone can advise on an effective way of achieving this entirely within VC?

The nearest I have got is running a Powershell Script that does the matching and assigning of fields into variables e.g.:
============
$currpath = "{TRIGGER(Active,LastTrigger,File.Result.TriggerFolder)}"
$currfile = "{TRIGGER(Active,LastTrigger,File.Result.Name)}"
import-Csv $matchfile | Where-Object {$currfile -match $_.FileMatch} | Select-Object -First 1 | Out-File $outfile | %{$fm = $_.FileMatch; $cID = $_.Cust_id; $appRoot = $_.'App Root'; $DDef = $_.DocDef;}
=============

This works extremely well, however there is no way of returning the individual values to VC (e.g $cID, $appRoot and $DDef in the script above) so they can be used like usual VC Variables elsewhere in the Job. Could this be an easy feature to introduce to allow me to keep all automation functionality within VC?

Cheers

Andy

Your request seems very specific. Without knowing all the details - if there was something in VC that could do this - how would it work? If you can break it down to something simple and generic then maybe we can do something. But currently I do not see exactly how this would work - but it could be that I do not understand the format.

---

Hi Henrik

Basically, we wish to take an input filename (e.g. abc123456789.dat into a VC variable) and pattern match it with an entry in a CSV file, for example:

================ csv file ===================
Xyz*.xml, app1, c:\files\mainfiles, y, y, n
Aaa???.*, app2, c:\files\backupfiles,n,n,n
abc*.dat, mainapp, c:\files\process\,y,y,y
================ csv file ===================

In this example, the first field of the third record (abc*.dat) matches the input filename, and so therefore, the subsequent fields are assigned to internal VC variables (e.g. {DocDef}, {filepath}, {flag1}, {flag2}, {flag3} etc)

Therefore a subsequent Task within the job will be able to issue a windows command, for example:

‘prog.exe /app={DocDef}, /Input={filepath}{Inputfilename}’

using the individual field values passed from the matched CSV record.

So somehow I need to be able to derive fields externally and pass them back into VC for further processing.

My example with using Powershell is a good example of being able to establish the individual variable values, but there is no obvious way of getting them back into individual VC Variables.

Hope that explains things better?

Cheers

Andy

You could use the normal Read file Task than use the Regex function against the content of that file. I am no expert on regex but it is extremely powerful to use to find a specific string.

---

Hi Henrik

Not sure how RegExp would allow me to first match a record in a csv file, and then extract each field in that matched record into individual VC variables. Like you I'm no expert in Regexp so it would be difficult to make this work in a simple way I think.

Some specific string matching, extract (on delimter) and replace tasks would be more suitable if possible.....

Cheers

Andy

I moved this topic to a an own topic as it took focus from the original topic.

I am not sure about implementation. Needs something really generic for this. For example, if your request had been;

"I have an .ini file and want to parse values from that".

Perhaps your request is to have something like the Excel Task, but for CSV files. So you can pick a specific row and column?

---

I am moving this topic (again) but now to Feature requests. We have a solution in mind where you could, in the Read file Task, do the following:

• Select input file type (similar to output type)
  
• Add a couple of Variables to this particular Task to retrieve a specific row and column from the output

I think those additions would solve the problem for you because you can call the Variable methods as many times as you want to retrieve any column in any line.

If you want to retrieve one column for all lines then you can already do this now by using the Loop functionality.

---

Hi Henrik

Yes, this would be ideal - being able to retrieve each field in any single record. I have been able to read a column for all rows using the loop command but I am looking for the reverse - read all columns for a single row and assigning each column to a VC Variable. :-)

Cheers

Andy

Hello Henrik,

I would appreciate to have this feature in Visualcron.

Regards Paul

Hi Henrik

Any news on introducing this feature? I have to provide a solution and would prefer to keep it all in VC, otherwise I need to use external applications to achieve the same thing.

Cheers

Andy

Hi Henrik

Any news on introducing this feature? I have to provide a solution and would prefer to keep it all in VC, otherwise I need to use external applications to achieve the same thing.

Cheers

Andy

Hi Andy,

while it is a nice feature we have a lot of other things to do on our priority list. If you are interested in prioritizing this then please contact sales@visualcron.com

Otherwise, we will update this when we come to this request and decide to continue with implementation.

---