Need to group output by Field to 1 excel file with multiple sheets

Question

Hello all,

I have about 5 input from different tables in SQL.

they all have different schemas, I need to group all the information into one .xlsx for each Country.

so far I have the following

the formula tool holds the following "C:\01. Projects\TESTING\"+[Country]+".xlsx|Cars"

The output for this first one works but i then need to add all "Homes" data and "clothes" data.

I need 1 excel workbook per country with all the inputs as separate tabs. I need a workbook for each country.

Please tell me it can be done. (I have attached dummy input data and how I would like the output to look like....)

Thanks in advance

Output Required by Country.xlsx

Input Cars.xlsx

Input Homes.xlsx

SPetrie · Answer

The more elegant solution would be a batch macro but it comes with a drawback.

You funnel all outputs into a union and the union goes into the batch macro.

Since all schemas get mashed into one, you have columns that are null in tabs they shouldn't be in. You can use a Data Cleansing tool to remove the null columns, but you run the risk of removing a significantly null column (meaning a null column that was supposed to be in the output) which is why it wasnt my first suggestion.

If you are confident that wont apply to your data or your output isnt adverse to that situation, it may be the better fit for you.

testflow.yxzp

ValR82 · Answer

@SPetrie - Thank you for replying! This is my current solution but with over 12 tables as data inputs and 13 different countries, i was looking for a cleaner (& hopefully faster) way of doing this.

SPetrie · Answer

You can do it. Since your output schemas are different, its best to use multiple block-until-done tools and output tools.

One for each tab schema. If multiple tabs share the same schema, you can use one output tool for them and just assign the tab name with a formula.

In this example, I just hard coded in the tab names since they are different schemas.

The formula you have for file name should work.