Dynamic XLSX Input Tool

[#1]

This macro takes a directory of XLSX files, and outputs all the data within each sheet into 1 output stream. This allows for multiple excel files to be unioned together without having to add multiple input data tools onto the canvas.

All you need to do is put the macro onto your canvas, paste the directory the files are located in, the row number the data starts on in each file, and if you want to include subdirectories.

This tool will only work with files where the data starts on the same line.

[Genevieve]

Hello,

Is there a way to select which sheets to include/exclude from the Excel files?

A lot of our Excel downloaded reports may be multisheet/multischema and include a 'Parameters' sheet which is not relevant.

I am struggling with creating a two stage batch macro which runs straight for .yxdb but should ask which sheets to include/exclude in case of Excel file.

[abacon]

@Genevieve I will look to add that functionality. In the meantime, you can open up the macro and add a filter after the GetSheetNames Macro to filter out irrelevant sheetnames. Thank you for the suggestion!

Bacon

[MojoRisen]

Hello, this is great, Thank you. What is the purpose of the Output Data Macro 19? Also, Was this intended to be ran as an app with the action tools?

[abacon]

@MojoRisen This is intended to be run as a regular tool you configure. If you pull the macro into a canvas, you will have options to fill out so you can dynamically get your data.

If it’s the macro I’m thinking about, it is the section that takes the full path (file path plus sheet name) and outputs the data into the workflow.

If you have it open, open another workflow, right click, insert, macro, and choose the macro. You will see more how its intended to run.

hope this helps!

Bacon

[abacon]

@Genevieve I don’t see a simple way to add the sheet selection functionality without making a chained app or something in like difficult. I would suggest opening up the macro, adding a filter in between the getsheetnames and output data macro, that will allow you to pass desired sheets through.

Bacon

[Genevieve]

@abacon Actually I think it might be feasible with a separate macro just listing the sheets then filter which sheets I want and join them back to file paths based on the filename.

Once again my IT dept is not allowing me to use the latest version of Alteryx  so can't fully test with your macro (yet).

[abacon]

@Genevieve 
Right click the downloaded macro(s), open with notepad, you can then change the version to the one you are on by simply changing the 2024.2 to your version.

I don’t believe I have 2024.2 functionality only in there so you shouldn’t have issues. If you do, let me know.

Bacon

[Genevieve]

@abacon 

Thanks,  For my own purpose I have made a couple of tweaks:

1.I added an output to list the sheet names in case the user wants to review (and run the macro twice).

2.I added a text box where the user can exclude sheet names they want to exclude directing a filter with the [Sheet names] not in ('Sheet1')

3. After the Get Data macro I split the sheet names from the full path  - because in a number of our cases the name of the sheet is a piece of data as well. REGEX_Replace([FileName], '(.*)\|{3}(.*)', '$2')

[abacon]

@Genevieve 

Love that! Great ideas.

Bacon