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

[Baz123]

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. :)

[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").

[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-Fi... 

[ArtApa]

@Baz123 - Here is a smore specific solution: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Reading-multiple-tabs-with-different-s... 

[Baz123 · Accepted solution]

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 · Accepted solution]

Hi,

Just updating - I have found a solution that works.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-on-Reading-in-Multiple-excel-Tab...

See binuacs macro - works!!!