Multiple files as input

[imivan]

My case have a directory with 18 different excel files and at least 7 sheets in each of the file. My task is to create a workflow such that they can use it as their template in the future to clean their data, however, I have received comment that says changing the input files every time is impractical and troublesome for them and they request me to update the workflow such that they can change the input files at once by changing the directory, however, they have different naming format for most of the sheets. I have been struggling to import all of them at once due to the naming format. Is the request possible or nah, if so, how to do so?

[imivan]

Note that the directory name is ever changing, let say for the one I working right now if it is C:\path\to\your\directory\XXX company\PBC\Q1'23, it can be C:\path\to\your\directory\XXX company\PBC\Q2'23 or Q1'24 next time. The files in the directory also has different naming formats.

[binuacs]

@imivan use the directory tool to read all the files from a particular directory then if you want to combine all the file use the batch macro

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/The-Ultimate-Input-Data-Flo...

[imivan]

Thanks man, will try, I am a beginner and it actually sounds super confusing to me but whatsoever, thank you for the suggestion and will definitely try it out

[imivan]

Hi, @binuacs, I noticed that the sample he provided has same sheet names for every files but mine don't, is there any suggestion for my case? Note that changing filename or directory name is not allowed as these are all PBC.

[imivan]

The sheet names and quantities are very different in my case. For example, there are 4 files: file A's sheets: Pivot - Input, Pivot - Output, etc.

file B's: Pivot and etc., which the sheet "Pivot" consists of both input and output, file C's: Input, Output, etc. and file D's: Report - Input, Report - Output, etc. As you can see, the sheet names are very different and there are many more of them with similar issues.

[binuacs]

@imivan In your use case multiple batch macro might be required. Can you provide some sample input files and the expected output file? 

[imivan]

@binuacs the data are large and also non-disclosure so I cannot provide. This is the most I can give: For example, there are 4 files: file A's sheets: Pivot - Input, Pivot - Output, etc., file B's: Pivot and etc., which the sheet "Pivot" consists of both input and output, file C's: Input, Output, etc. and file D's: Report - Input, Report - Output, etc. So, if I have to do multiple macro batch, is it based on the naming like since A,C and D has the word: Input and Output inside so I will have to create 1 for input, another for output, then create another macro batch for file B: Pivot, and so on?

[binuacs]

@imivan What is the expected output? Do you want to combine all the sheets and the files into one ? 

[imivan]

No,  @binuacs so the output are varied as well, normally needs 3 outputs but some of them only need 2. The problem is the input stream as I was requested to use the directory as the input source however the file names, sheet names in the file, sheet amounts in the files, column structure and schema structure are all very different. The request I received is to union the input stream such that when the end user wants to upload a set of different input data, they only need to change the directory instead of the files one by one.