Reading multi-tabbed excel from temporary path

[nimeshkhatri]

Hi, I have a workflow downloading a multi-tabbed excel from SFTP to temporary file path on my system. I am not using absolute path since I want to deploy the solution to gallery and schedule it. Further, I tried to use a dynamic input tool to read the excel file. This step fails because I need to select at least 1 tab as template when reading the temporary file. When I click the 3 dots to select a tab, I get an error that excel can't be read, probably because it is a temporary excel file which is not their after a run completes.

Can someone share alternatives of resolving this?

[ChrisHe]

Hey @nimeshkhatri  - is the data downloaded from the SFTP typically the same format? If so you could upload a copy of one of the typical files up with the workflow by checking it's box under Workflow Optipons>>>Manage Workflow Assets when saving to the Gallery.  That way the Dynamic Input would use the templated file in the template section and then read in the multiple tabs from the SFTP download.

Another alternative could be using a Directory Tool and a Batch Macro to read in the multiple Excel sheets as shown in this community article.  That method has the advantage of not worrying about differing schemas.  You'd just need to make sure to package the batch macro up with the workflow in the same way I mentioned for the templated Excel file under Workflow Options.  

Let me know if you have any other questions and please mark as a solution for others to find while searching if this helped!

-Chris

[nimeshkhatri]

Thanks for responding @ChrisHe About the first point, I first have to get the workflow working in local designer version and with temp file I am not able to do that.

About the second point, yes there is a way to read multi-tabbed excel but the problem here is to read that temp file with multiple excel and select one as pattern. That said I'll check the macro solution referred and see if that doesn't run into the same issues.

Edit: I checked and the directory tool will also need an absolute location. In my case, it is generated at run time since I am using temp files.

[ChrisHe]

@nimeshkhatri - I know I won't have access to your SFTP location but would it be possible to share some version of your workflow? How are you downloading the SFTP file? Download Tool, Run Command or some other method?

Also does the format of the downloaded file change every time the workflow is run or is it standard? 

[nimeshkhatri · Accepted solution]

@ChrisHeI was able to select a dummy file of the same format and then replace it with temporary file, that worked. Thanks for your inputs.