Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Multiple tabs different schema excel input to Alteryx

sureslala
8 - Asteroid

Hi Community

I'm investigating how to input an excel sheet with multiple unique tabs into one Alteryx workflow. There is a lot online regarding this subject and I've found a macro that appears to work but I'm having trouble seeing my separate tabs within my workflow so that I can  start analysing them.  If there's a more appropriate solution, then I'm open to that also. I will actually will have a input file for each month so eventually need to analyses 12 input sheets (each with 5 unique tabs), which I want to consolidate using the summary tool.

 

sureslala_0-1650886530612.png

 

In attached workflow, I can run the macro but I can't break the data down to see all 5 individual tabs in separate tools so that I can modify each one.

Thanks
Sures

 

 

5 REPLIES 5
ChrisTX
16 - Nebula
16 - Nebula

Can you include the macro in your workflow?  Use Options > Export Workflow to create a yxzp file, including the workflow and macro.

 

Chris

sureslala
8 - Asteroid

Hi Chris
Pls see attached, think this has worked, pls advise if this is ok.

 

Thanks

Sures

ChrisTX
16 - Nebula
16 - Nebula

I'm guessing you've seen these posts:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

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

 

And this one is helpful: https://www.thedataschool.co.uk/nick-jastrzebski/beer-and-loathing-using-a-batch-macro-to-import-mul...

 

It sounds like you're trying to read in multiple Excel files, with multiple sheets, with a different layout on each sheet, and combine the data from same sheet names, from the various Excel files.

 

 And it sounds like you want separate data streams for [the output of the combined data from the same sheet names]

 

If you feed in file names and sheet names into a batch macro, the macro will stack records on top of each other, from the same field names in the various sheets.  It doesn't sound like you want data from different sheets to be combined.

 

In your case, I think you should.....

Read in all of your file names and sheet names using the Directory tool

Separate your single Directory output stream into 5 separate streams using 5 Filter tools (sheet name = "Mar ME_Mkt Ops", sheet name = "Basis Curves_Instrument", etc)
Use 5 Dynamic Input tools

Alternatively, you could....

Use the batch macro like you're doing now

Hope that Alteryx imports [sheet 1, field a with data type 1] as the same data type on sheet 2.  (since the data will be stacked from different Sheets, based on field name)

Use 5 separate Filter tools to separate your one data stream (from the macro) into 5 separate streams (sheet name = "Mar ME_Mkt Ops", sheet name = "Basis Curves_Instrument", etc)

Use 5 Data Cleansing tools with option Remove null columns, to get rid of columns that "don't apply" to specific sheets.  But this could inadvertently delete a field you want to keep on a specific sheet.

 

Chris

sureslala
8 - Asteroid

Hi Chris

Using the 'Directory Tool' can I bring into my canvas multiple excel files and all their separate excel tabs? I want to bring in 2 files each having multiple tabs. I was then going to use the filter tool to separate each tab?

sureslala_0-1650981344195.png

 

Where have I gone wrong here pls?

Thanks

Sures

 

ChrisTX
16 - Nebula
16 - Nebula

Is your Directory tool returning 2 records or 0 records?  The screenshot doesn't show the Results pane.

 

In Windows File Explorer, it might be nice to show your file extensions.  Use View > Options > Change folder and search options > View tab at the top > un-check box for "Hide extensions for known file types".  Then click the button "Apply to Folders" at the top (and Yes in the pop-up), and click OK.  The new view setting will apply to all folders, then you can see the full file name including the extension.

 

See https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-List-of-Excel-Sheet-Name...

add a Formula Tool before the Dynamic Input to your FilePath Field
Use this formula:

[FilePath] + "|||<List of Sheet Names>"

 

See attached workflow

In the last 2 tools, click the button for Edit and point to an example template Sheet from one of your files.  With the Dynamic Input tool, if any Sheet from a different file has different columns, you'll get an error.

The format of sheet "Mar ME_Mkt Ops" in file 1 must exactly match the format of sheet "Mar ME_Mkt Ops" in file 2.

If the formats are not the same, you'll need to use a batch macro.

 

ChrisTX_0-1650984840497.png

 

 

Chris

Labels
Top Solution Authors