Merge sheets into one - exclude one sheet, start on third row...?

Hi,

I have followed a couple of examples on here where users have combines sheets using the below method which I have replicated locally and had success:

My issue is that the data file I am trying to replicate this solution is has a couple of quirks that are failing.

The attached is the example data sheet.

Firstly - each sheet starts on row 3 (header row).

Secondly - there is a tab called macro which I want to exclude. Is it possible to have the workflow only read all sheets where name is like *Sheet*?

Thanks for the help, I am stuck on the caveats on this one. I cant see where others have had a similar issue.

Multi Sheet example.xlsx

Datasets

Input

Preparation

Accepted answers

Baz123

HI @Yoshiro_Fujimori ,

Yes, the problem occurs on the real data - it appears that Alteryx sees it as each tab as having a different schema. The test file works fine. I can't seem to get any macro solution to work properly when adding it to the workflow you have shown.

@ArtApa - thanks, ill need to read a lot to try to get something working. I know the principals of adding a macro, just cant seem to get one to work...gah!

Baz123

Hi,

Just updating - I have found a solution that works.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-on-Reading-in-Multiple-excel-Tabs-that-have-Different/m-p/1057862/highlight/false#

See binuacs macro - works!!!

All comments

SPetrie

In the setup for your dynamic input, you can specify the row to start importing on. It defaults to 1, so just change that to 3 and you should be good.

For the sheet called macro, you can add a filter tool just before the dynamic input and filter out that sheet name or any other sheet name you dont want to import. contains([sheetname],"Sheet") or something along those lines would do it.

Baz123

Hi @SPetrie, Thanks a lot for the help - that did the trick. I am able to import the sheet names, and filter out the tab not required.

This now creates a new problem I need help with . I have the Input data tool going to the filter tool. When I add the dynamic input tool in after the filter it reads the first sheet in (65K rows), then gives the error for all other sheets:

Error: Dynamic Input (10): The file "<File Path>" has a different schema than the 1st file in the set.

and so on for each tab.

Each sheet is exactly the same as shown in the initial post example?

What am I missing here? I have looked around and some say to use a macro, but I cant get it to work???

Thanks

Yoshiro_Fujimori

Hi @Baz123 ,

Is your problem occurs only when the first sheet (65K rows) are included in the file?

Using your sample data, I successfully imported all the sheets (except for "Macro").

DynamicInput.yxmd

ArtApa

Hi @Baz123 - If it says that Sheets have different schema then they have different schema. It can be tricky with Excel, as not everything you can see with an eye. They way you can solve your use case is a batch macro instead of Dynamic Input. Here is an article on the topic: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

ArtApa

@Baz123 - Here is a smore specific solution: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reading-multiple-tabs-with-different-schemas/m-p/391012/highlight/true#M71630

Baz123

Hi,

Just updating - I have found a solution that works.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-on-Reading-in-Multiple-excel-Tabs-that-have-Different/m-p/1057862/highlight/false#

See binuacs macro - works!!!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235