Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Processing Single Excel with Multiple Tabs

Rayan
6 - Meteoroid

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.

7 REPLIES 7
HomesickSurfer
12 - Quasar

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
6 - Meteoroid

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
12 - Quasar

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.

 

Capture1.PNG

atcodedog05
22 - Nova
22 - Nova

Hi @HomesickSurfer 

 

Looks good, should work for the usecase 🙂👍

Rayan
6 - Meteoroid

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
6 - Meteoroid

@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
12 - Quasar

Hi @Rayan 

I'm pleased that it works for you!

Accept as solution if willing.  Thx

Labels