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

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!

Input

Dynamic Processing

Accepted answers

mceleavey

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:

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?

Multi Sheet Import Example.yxzp

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Flat Files.yxmc

All comments

mceleavey

Hi @dbuz ,

I built a couple of tools to do exactly this.

I've attached the two tools and an example workflow:

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?

Multi Sheet Import Example.yxzp

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema Flat Files.yxmc

dbuz

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

Hi @dbuz ,

Yes, my solution should fit your needs perfectly.

dbuz

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!

mceleavey

@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:

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:

dbuz

@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!

attachment2.PNG

attachment1.PNG

mceleavey

@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.

dbuz

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

no problem.gif

dbuz

@mceleavey,

I was wondering if I could bother you one more time. Would you also be able to advise on how to insert a regEx inside the "Import Multiple Schema Flat File" macro which imports all files within a specified folder? I would like Alteryx to only upload files named "File123_dateoftoday" instead of all of them. I am trying to edit the macro, but I have no experience with them so it seems quite complex to me.

Thanks one more time!

mceleavey

You don't need regex, and you don't need to mess with the macro.

After the Directory tool, simply parse out the date from the filename and apply a filter tool where the file date = datetimetoday()

You may need to format the date when you parse it.

dbuz

Good call! Will do it right away, thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703