Excel file dynamic input with different schemas and different sheet names

[Peter4]

I have a number of Excel files which have different schemas and different sheet names. I'd like to import them all at once from one directory and let the user select the sheets they'd like to import.

Additionally, the headers can be on different rows so they need to be pushed down into the data.

I have tried using the Wildcard XLSX Crew Macro, but unfortunately it outputs my data into two different streams and doesn't give me an option to push down the header into the first row (or at least it doesn't work for me, no matter how I configure the macro). This works as long as there's only one file in the non-N output, but it would break my workflow if there were two.

Instead of having two output anchors, is there a solution which unions all files immediately? Even if they have a different number of columns, different file names, different sheet names and the header is on a random row?

[fmvizcaino]

Hi @Peter4 ,

Take a look at the attached example. The idea is to have a macro that reads all xlsx files in a folder, then by using the sheet finder macro, to read all tabs from all excel files, then by using another macro, to concatenate all of them even if the header is in different rows.

Let me know if this works for you.

Best,

Fernando Vizcaino

[Peter4]

Hi @fmvizcaino,

Thank you for your swift reply. I tried running the workflow, but get an error message for the OneFileAtATime2 macro. It says "The file is not a valid macro". Do you think you could package the workflow as version 2018.4?

Thanks

[fmvizcaino]

Hi @Peter4 ,

I have changed the versions now, let me know if it is working.

If not, please send me some screenshots to help me understand your problem, please.

Best,

Fernando Vizcaino

[Peter4]

Thanks for that.

It is indeed working, but adds all the columns next to each other. I'd like to have one columns with the full path and the one next to it should be F1, F2, F3 etc. I have attached a screenshot of what I mean. I've highlighted the headers of my files. Basically, the Crew macro is almost perfect were it not for the two outputs. 

[fmvizcaino · Accepted solution]

@Peter4 ,

Attached is a new version with your requests. See if this accomplishes your expectations.

Best,

Fernando Vizcaino

[Peter4]

Yes, thank you very much!!!

One more question, would it be possible to exclude sheets I don't want to import in the macro input? I could do that using a filter later in the workflow, but that's not the most user-friendly option for the stakeholder, because I don't want them to mess around with functions and they may not know about 'contain'.

[fmvizcaino]

Hi @Peter4 ,

You can eliminate tabs by using a filter in the part showed below.

In this case, you need to know the exact tab name to remove from your workflow.

Let me know if this helps you and please mark the answer as correct if you got what you were looking for.

Best,

Fernando Vizcaino

[Hi2023]

Hi,

I have the same issue but the the MACRO para input is giving an error?

could you please resubmit 

thanks!