Input multiple Excel sheets/tabs to one

[melissadata]

How do you input multiple sheets/tabs (about 200) with identical columns and output this into one data sheet?  I've read a few articles referring to this but it's only referring to if you're looking at including only a portion of those sheets, not all of them.  What's the best way to do this?

Thanks!

[AndréB]

Hi @melissadata,

This is a perfect scenario for dynamic input. I built out a basic workflow for you to reference. 

1. Output the list of sheet names
2. use dynamic input to change the file/table name while using any tab from your spreadsheet as a source template

Let me know if that helps!

-AndréB

[melissadata]

Hi Andre,

Thank you so much.  For the dynamic input it says to "use a template worksheet to specify the schema (shape) of the tables, etc..." I just want to copy all of the columns from each tab in the one spreadsheet and then output it to one spreadsheet.  I don't even need to reference which tab they came from, just need the data on one tab.  Could I skip that next step?  Would that be possible?

Thanks again! 

[melissadata]

When I upload the files to the template you provided it will pick up the first tab then say, "directly|name1" has a different schema than the 1st file in the set."  I built out the same thing using input and then dynamic input and received the same error.  All of the columns are all the same and nothing is different between any of them.  Is there something I should add or refer to in the dynamin input?

Thanks again!

[AndréB · Accepted solution]

Hi @melissadata,

To answer your first response. You do not have to output the source of the data. If you click "edit" in the "Input Data Source Template" section of the "Dynamic Input" tool. Option 5 where it says Output File Name as Source, is where you can configure that. Just select the dropdown and choose "No".

To your second response. I'm able to duplicate your error message, but only when the schema in a tab within my spreadsheet isn't the same as the schema in the tab being referenced as the "input data source template"

Is your workflow bringing in any data at all from your spreadsheet? Maybe try removing the tab that is erroring out from your dataset as a test?

-AndréB

[melissadata]

Hi Andre,

I went ahead and tested out the spreadsheet to try and determine why each of the tabs resulted in an error message with a different schema.  All of the columns are identical so it was confusing.  I ended up creating a new row for each tab and used a numerical value as the header.  That ended up working.  

Thanks again!

[jf97hernandez]

Hello Melissadata,

How exactly did you fix the error? I have the same names for the columns in each tab. Did you add a row at the top and added numerical values to the columns? As is "1" "2" "3" etc?

Please let me know as soon as you can!

Thank you in advance for your time and cooperation.

Best,

jf97hernandez