This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am a newbie with Alteryx and was wondering if you could help me with my problem. I know a lot of questions like this have already been asked, but I couldn't find a thread that meets my needs.
I would like to load several sheets from different xlsx files from a directory into Alteryx, but the problem is that the second sheet of each excel file does not have any header, so I would need to copy / paste the header of the first sheet into the second, only if the second sheet present some data inside. Sometimes I work on files that have only one sheet with data, where this process is not required and other times I work on files with multiple sheets and this should only apply to those files.
With that done, Alteryx should load all sheets from all files and consolidate all data into one dataset, which I will work on.
Any kind of help in this regard will be greatly appreciated.
The problem is that when you try to use the Dynamic Input tool you need to select a template, which the common format of each file input. If the schema changes, for example different columns exist, then the dynamic input tool will error.
I've attached the two tools and an example workflow:
The first tool reads in every sheet name within each workbook and adds it to the filename as you need this when loading in Excel files. This then gets joined back to the main stream to be loaded into the second tool:
The second tool uses the fullpath field, which now includes each sheet name as well and loads them all in.
You can place a filter after the first tool to only select the sheets you want if required.
I've attached an export of the example workflow (the .yxzp file) which you can use if you're on version 2021.2 or higher.
If not, you can open them all manually and save the macros to your macro folder.
Now, you have an additional problem of the data having different column headers across different sheets.
To get around this you will need to provide an example of what you mean. Can you post some data as an example of the data being the same across multiple sheets but with different headers?
Thanks @mceleavey ! I'll try your solution right away! Much appreciated.
As for your question, all the files I want to upload have the same headers and would follow the schema below. The only aspect I would like to point out is that some files may or may not have different sheets. Only files that have different sheets (not empty) do not have headers in the sheets following the first one. With those, Alteryx should insert a blank line at the beginning with the Sheet #1 headers.
Schema as below - always 12 columns with X number of rows.
Unfortunately, I am unable to install the latest version of Alteryx and I am still on the 2019.4 version. My PC is blocking all software installations. Therefore, I cannot install the new version, nor manually save the macros - I assume that with the sentence below you were suggesting me to save the macros in the system folder where Alteryx saves its installation files. I cannot access these system folders too as they are restricted.
"If not, you can open them all manually and save the macros to your macro folder."
Are you aware of any workaround? By any chance, would it be possible to build the same macro using my Alteryx version? It would solve all my problems! Or are you aware of any other way in which the intended result can be achieved without using macros?
I followed your instructions, however, I am still facing some issues. I saved your macros to my PC as you suggested, but apparently the problem is with the Alteryx workflow package "Multiple Sheet Import Example". When I try to import it, Alteryx throws an error, which prevents me from loading it into Alteryx. Please see "attachment1".
Even if I click "ok" and ignore it, the workflow does not get loaded into Alteryx.
Therefore, when I open the "Example of Importing Multiple Sheets" workflow, it seems that Alteryx does not recognize any of the macros - attachment2.