Hi All,
Need help on how to read data from an excel file, if the relevant data can be in any of the sheets in the file? For readexcelsheet() if sheet number 'x' does not exist it would throw an error, so how to traverse through sheets?
Discussion posts and replies are publicly visible
Have you tested with readexcelsheet() in a process or SAIL form? In my tests, this function errors cleanly and would allow you to build a loop to check any number of sheets. Running this in a script task against an excel file with 1 sheet:
readexcelsheet(cons!CHRIS_TEST_SP_DOC,150,1)
The script task completes successfully and provides a dictionary with values for 'success' and 'error' message:
[success:false,error:Sheet index (150) is out of range (0..1)]
Running the function on sheet 1:
readexcelsheet(cons!CHRIS_TEST_SP_DOC,1,1)
The result is a similar dictionary with success=true and now a result parameter:
[success:true,result:[data]]
So, build a loop that checks any number of sheets, if success is false, move to the next sheet and try again until you reach success=true.
Thanks, Chris for the explanation. I have a query on how to run a loop based on a condition in Appian? The for-each() would run a loop based on a number of elements in a list, the same goes with reduce().
But how can I run a loop based on a boolean condition and do value increment on each iteration?
As in the case you suggested, I need to perform a loop every time I get a true from the excelsubset.success and also to increment the sheet number for each iteration.
Hi Saahil,
If you want to implement in process, it's pretty simple, You do not really need a!forEach there. You can use a combination of script task (For incrementing the sheet number), another script task (For reading the file), and then an XOR (to check if the script task has returned true in success) and with that, you should be good.
Cool, was thinking of an expression rule. But this is good. I Will try this and update, thanks Harshit.
Harshit, it is working. Thanks for helping.