LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

Is it possible to acquire data from a file in an unknown location?

I mean, if we don't actually know where the input file would be or which name it will have.
I came across a "solution", which it will be to load a plain text file, which will have the name of the final file in it.

What I can't do right now is to use that line as the file name for an Excel File Node. Is it possible to do it?
Could you please advise if there is a better solution for this?


Thank you in advance,
Daniel

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Add a node to read the text file. Connect that output to the Excel node (adding an input pin to the Excel node), and then on the Excel Node, use the FilenameExpr parameter to indicate which field holds the filename.

One extremely common method is to use a DirectoryList node to read the filenames in a folder, then a filter to help decide which file(s) to process, then passing that list of files into the Excel node.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

Thank you Stony for your answer.

However, when I do that I get this error:

"Parameter FilenameEXpr references the file c:\Lavastorm\LAETemp\tmp\admin\.....\C:\Users\User \Desktop\test.xlsx. However this file does not exist on the file system"

It is taking the path from the temporal folder that Lavastorm uses. Can I somehow change it?

Thanks and regards.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

What did you to generate the filename (column) that you sent into Excel?
Double-click on the input pin of the Excel node, and examine what value is in that field.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

The data on the input pin is the following: one field called 'Path'.

Its value is as follows: \\\\C:\\Users\\User\\Desktop\\test.xlsx

In the FilenameExpr I just added the input Path, and when running the Node, the same error as said before appears.
I also tried creating a parameter in the Excel File and using it in the FilenameExpr field as {{^test_input^}}

The same error again.

Thank you

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: aop

Remove \\\\ from the beginning. If you now have a node that outputs \\\\C:\\Users\\User\\Desktop\\test.xlsx as string to e.g. field FileName put FileName as value for FilenameExpr parameter in your excel node.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dcoll

Thank you aop, now it worked for me!!

The next part I need now... I finally did it through a directory list. I have now 4 files (for example). How can I now perform the process that I made after the Excel input node for every file? I mean, I just have one process, which I want to be run as many times as files I have. Each file will give different values, and I would like to output each in a different file, changing its name from a substring of the file name.

Is it possible to do that? I believe that can be done with a do-while node, but I can't find the way to make it happen...

Thank you in advance!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: aop

If the structure in your 4 excel files is similar then you can take all the data in in one excel node (I guess this is how you've done it already).
To help things later on you could specify a custom workbook spec in the excel node to generate a field containing the source file nime in a field in the output. So in its simplest form you could put this as the value for WorkbookSpec parameter:
As next step you would have your processing logic in LAE. I understood you would just like to process the data and create separate outputs for the processed data based on the filename the row originated from. Idea so far was to handle the data from the four source files as one data set (no need to loop the processing logic). This was made possible by including the source file name in the data so we can separate the data sets in the reporting phase.

When it comes to the reporting side you have at least these options depending on your scenario:
1) If you will always have the same number of files with same names then you could just create a filter in the end of the chain with multiple outputs and have four output nodes after that. Filter criteria would be based on the filename field you have in your data now.
2) If you wil always have the same number of files but names can differ then you could again use a filter but as the file names would vary the filter criteria could be based on mapping a source file name to a number (e.g. first unique filename in filename field to 1 and the last to 4) and and then creating a filter with four outputs using the numbering as filter criteria.
3) If you will have a varying number of source files you would need a loop construction. Search for IterationExample.brg for loop examples (should be a part your LAE installation).
4) As a good alternative to the loop structure you could use Execute BRX node that can also be used to loop a certain processing logic but is a different approach from the loop node. If you would want to have output file naming to be based on the source file names (and the file names differ from time to time) or some other dynamic factor this approach would allow also that. However, figuring out the first time how this node is set up could take a while...