Is it possible for user to indicate1 file but import all sheets (different schemas)

[AkisM]

Unfortunately I can't use the "import sheet names" and then dynamic input or something like that, both because of different schemas, and also because the file imported is .xls and that doesn't give you the option to import sheet names.

I was thinking something along the lines of, the path that the user enters is saved somewhere, then that path is pasted on the other 3 input tools to import the other 3 sheets, and the sheetname is appended with a formula tool or something (since it is hardcoded and will always be the sheetname).

So does anyone have a workflow idea of how with 1 user file browse tool you can feed 4 different input tools to get 4 different sheets from the same file?

[Jonathan-Sherman]

Hi @AkisM,

Could you use the directory tool, filter out file paths/sheets you don't want from the file? And feed this list into a batch macro?

Regards,

Jonathan

[BenMoss]

No need to over complicate this; you can have 4 different action tools off the same file browse tool to update only the file path in (and not the sheetname) in each input data tool.

Obviously the sheet names have to be the same, but you seem aware of that.

Worth noting this behaviour would not work on Alteryx Server.

[Hugo]

Hi @AkisM 

It's not totally clear what you want here.

If this is a one-time thing, I'd save the file as an xlsx file and either use the CReW macro 'wildcard xlsx input', or get the list of sheet names and use a batch macro to import all the (non matching) sheets of the file.

If it is a process you will be repeating, sheet names will not change, you could append the sheet names to the file path of the file, then use a batch macro to import all the sheets there.

Check out these posts for inspiration:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Batch-import-files-with-varying-schema...

[AkisM]

@BenMoss I think this is what I was looking for. I totally forgot you can just add multiple actions to 1 file browse. So I will have 1 file browse tool, and 4 actions+4 input tools to deal with the 4 different static sheets. Any ideas on what the configuration of the action should be, so that the user input changes only the path, but I can keep the sheet names which are static hardcoded in my workflow? So far I've only been using the default configuration of the action that comes after the file browse so I'm not very familiar with the other options. Or do I still keep it to default and I have to configure my input tools differently?

For example one time the path would be:

C:\Users\John\ExcelFile Nov 2019.xls|||'CalculationsSheet$'
C:\Users\John\ExcelFile Nov 2019.xls|||'WorkingsSheet$'

C:\Users\John\ExcelFile Nov 2019.xls|||'FinalSheet$'

then another time it would be:

C:\Users\David\RandomExcelFile Dec 2018.xls|||'CalculationsSheet$'
C:\Users\David\RandomExcelFile Dec 2018.xls|||'WorkingsSheet$'

C:\Users\David\RandomExcelFile Dec 2018.xls|||'FinalSheet$'

[danilang · Accepted solution]

Hi @AkisM 

To implement @BenMoss's idea, you could use something like this

 

Each of the Action tools is configure like this

with the sheet names in the formula differing for each action tool.

Dan

[AkisM]

@danilang Hi, haven't tested that yet but it looks right to me and I take your word for it 😛 Thanks for all the help to everyone.

[AkisM]

@danilang Tried and works. I have another issue in the gallery though, would be grateful if you could shed some light on it.

When I the user browse to the excel file with the 4 static sheets by using the analytic app in my gallery, it also asks me to select a sheet. Is it possible not to prompt the user to select a sheet? If not possible, my understanding is that no matter which sheet the user picks, the formula we used in the action tools guarantees that it will pull the 4 sheets we hardcoded it to look for, correct?

[danilang]

Hi @AkisM 

The prompt for the sheet name is determined by the file type and happens in designer and on the gallery.  There's no way to disable it.  Though I haven't tried it on the gallery, the formulas should override the inputs and select the 4 separate sheets.  

Dan

[AkisM]

Thanks for the clarification @danilang . One last question. I just noticed that one of the sheets I'm interested in isn't 100% static. They append a number at its end which is month+year. For example:

Sheet: VAT Output 1019 (referring to october 2019)

VAT Output 1119 (referring to november 2019)

etc.

Is it possible to edit the formula in the action tool to take the sheet called "VAT Output" regardless of what number it's followed with? Kinda like hardcoding part of the sheet name and leaving the rest of it as a wild card.