Processing Single Excel with Multiple Tabs

[Rayan]

I have an excel input file with 6 tabs. Each tab has a certain data range and there will be independent processing for each range. I wouldn't want to specify the file multiple times for each process in a separate input connection. Is there a way to specify the excel file in just one input and execute 6 independent flows against different tabs?

Further, filename and path need to be dynamically derived based on some logic say T-2 where T is current date. I am hopeful this can be achieved once above problem is solved.

Thanks.

[HomesickSurfer]

Hi @Rayan 

I am not on my PC, but it is quite possible.  I do it often.  In the interim, can you confirm if your source files are all in the same directory, same naming convention with either prefix or suffix, same file type?  Are the sheet names all the same across the multiple files and do they have the same structure between files?  Eg.  File 2018-08-05.xlsx.

[Rayan]

Yes, file name will be like 2018-08-05.xlsx. Each file will be in its own directory in yyyymmdd format. Apart from fetching the file dynamically, I need different tabs from same file to be used as source for concurrent processing. Thanks.

[HomesickSurfer]

Hi @Rayan 

Bingo! This should work.  Modify to suit your needs.

See below and attached package.  Extract to C:\temp to test.

If it meets your requirements, please share, bookmark, 'Like' and/or 'Accept as Solution'.  Thx.

@atcodedog05 , if able and willing, have a look and advise of enhancements, if any.  Thx.

This flow identifies xlsx files dated 2 days ago, generates 6 sheet paths, feeds into a dynamic input and isolates your 6 sheets' data that you can tap into for downstream processing.

[atcodedog05]

Hi @HomesickSurfer 

Looks good, should work for the usecase 🙂👍

[Rayan]

Thanks @HomesickSurfer  and  @atcodedog05. Just to clarify, there is a single existing excel file with 6 tabs (can be any number, but just 6 as an example). Each tab has a different schema. In each tab, there is a specific range from which data should be pulled and transformed separately. What is the best way to set this up? I want an easy way to replace the file in all 6 concurrent flows in one shot (if it can be made dynamic based on the last update time it's good, but this is not a primary concern at this point. I can manually replace the file name if it's centralized). Source range for each tab is fixed and known upfront. Appreciate your time.

[Rayan]

@HomesickSurfer Actually, you provided the clue in sample. Sheet name needs to be appended to the excel file using Formula. Once the file name is dynamically generated, I can append specific range and branch off to 6 different flows. Thank you.

[HomesickSurfer]

Hi @Rayan 

I'm pleased that it works for you!

Accept as solution if willing.  Thx