Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Input multiple Excel files with multiple sheets with same layout but without header

dbuz
7 - Meteor

Hello everyone,

 

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.

 

Thanks in advance!

12 REPLIES 12
mceleavey
17 - Castor
17 - Castor

Hi @dbuz ,

 

I built a couple of tools to do exactly this.

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:

 

mceleavey_0-1623091657337.png

 

 

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:

 

mceleavey_1-1623091657331.png

 

 

The second tool uses the fullpath field, which now includes each sheet name as well and loads them all in.

 

mceleavey_2-1623091657327.png

 

 

 

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?

 

 

M.



Bulien

dbuz
7 - Meteor

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. 

 

A             B             C             D             E              F              G             H             I               L              M            N

1              2             3              4             5              6              7              8              9              10           11           12

…           …          …          …          …          …          …          …          …          …          …          …           ...

…           …          …          …          …          …          …          …          …          …          …          …           …         

 

mceleavey
17 - Castor
17 - Castor

Hi @dbuz ,

 

Yes, my solution should fit your needs perfectly.

 

M.



Bulien

dbuz
7 - Meteor

Hi @mceleavey  thanks for your prompt replies!

 

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?

 

Thanks again!

 

D.

mceleavey
17 - Castor
17 - Castor

@dbuz ,

 

open the two macro files in Alteryx, you may get a warning saying it was built in a later version but you can accept it and move on.

Then save them both into your macro folder.

 

To set a macro folder, go to Options->User Settings->Edit User Settings:

 

mceleavey_0-1623234179340.png

 

mceleavey_1-1623234202308.png

 

If you don't have a folder designated here, click the "+" sign and navigate to a folder where you want to store your macros.

 

Save the macros in this folder and you should then see them in the "In/Out" section:

 

mceleavey_2-1623234274765.png

 

M.

 

 

 

 

 



Bulien

dbuz
7 - Meteor

@mceleavey,

 

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.

 

Would you by any chance know how to solve this?

 

Thanks!

mceleavey
17 - Castor
17 - Castor

@dbuz ,

 

you haven't saved the macros in your macros folder, or if you have, you may need to replace those missing tools.

 

The first one is the Dynamic Multi Sheet macro, and the second is the input multi...

 

Check you've saved them correctly by confirming they are in your "In/Out" section.

 

M.



Bulien

dbuz
7 - Meteor

Thanks @mceleavey !!

 

Your solution works perfectly.

 

FYI - I was able to fix the problem with your macros by manually editing the second line of them in notepad and changing the version.

 

FROM:

 

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2021.2">
<Nodes>
<Node ToolID="1">

 

TO:

 

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2019.4">
<Nodes>
<Node ToolID="1">

 

mceleavey
17 - Castor
17 - Castor

no problem.gif



Bulien

Labels