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.
SOLVED

Regarding dynamic excel Sheet name and dynamic worksheet name

nazuk
8 - Asteroid

Sorry for reposting again! But since i am a beginner to alteryx. I am still facing issue in inputting my file. So basically the scenario is there will be a folder inside which there will be 4 files with different name. So i have to use one file named in the format as TSEQ_DD_MM_HHMM.xls  and there will be just one worksheet inside this xls file with the same name as file name.

 

So i have to use that file and then have to do some analytics. Now since this sheet name will change daily , as it is generated from some tool, can you again help me with some workflow , how i can code the input method that may  pickup the static keywork TSEQ_ and dynamically chooses that file daily(as the workflow i am preparing will run daily.

 

Looking forward to hear from you.

6 REPLIES 6
neilgallen
12 - Quasar

your solution is not that uncommon, but there are a few questions that need to be addressed:

 

You mention inside this folder there are 4 files and you want the one file formatted with TSEQ_* My assumption is that these files are timestamped. Will there be multiple files with the TSEQ prefix in the folder, each with a unique timestamp? If so, do you want all of the files, or just a particular one?

 

Are they generated as xls or xlsx files? I ask because this changes what you are capable of with sheet names.

 

Could you post an exmaple of the four files in the folder? 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @nazuk,

 

I've attached a zip file with all materials I explain below:

 

Starting with the main workflow I bring in all filepaths of .xls files stored in a particular folder using the directory tool, remove unnecessary columns and filter for where the filename starts with "TSEQ_". I then create the full file path (including sheetname) as you stated it is identical to the name of the file.

 

This "new full file path" is fed into the batch macro to import the data.

 

image.png

 

 

The batch macro itself has very few tools in (just used to feed in a filepath into an input data tool):

image.png

 

This gives me the resulting output from the "TSEQ_" file in the directory selected:

image.png

 

Simply point the directory tool in the workflow at the correct location:

image.png

 

If this solves your issue please mark the answer as correct, if not let me know! I've attached a zip file containing all files needed (workflow, macro and two text .xls files) for you to download if needed.

 

Regards,

Jonathan

 

 

 

nazuk
8 - Asteroid

Hello, Thanks for the reply.

 

There will be just one file with name TSEQ*. Yes that * is time and date stamp. It is .xls file.

nazuk
8 - Asteroid

Hello Jonathan!

 

Appreciate your quick response. The explanation is quite informative as well.

 

Just wanted to check, i have placed the macro at the shared drive, and Added the location in user setting as:

 

nazuk_0-1585847624759.png

 

 

Still i am getting an error as unable to find macro.

 

nazuk_1-1585847715121.png

 

 

Please suggest!

 

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @nazuk,

 

You'll need to open up the workflow and replace the macro (right click on the canvas of the workflow -> insert -> macro -> browse -> navigate to the macros location):

 

image.png

 

Also make sure you've configured the macro correctly and you're passing the [FileName] field into the batch macro:

 

image.png

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan

seven
12 - Quasar

Please check my post. I have solved this in a completely dynamic way.

 

Fetch sheet names dynamically 

Labels