Multiple SQL Queries into 1 excel file with sheets

[olimpio]

Hi everyone,

I have a  workflow which I need help with.

Scenario:

We have multiple SQL queries that need to be queried. In the current environment the workflow will pick up the queries 1 by 1 and create one excel file for each output.

requirement:

We need one excel file with multiple sheets for each query instead of having multiple excel files. is there is any way of getting this done?

[gabrielvilella]

I’m guessing you have the output tool inside a batch macro. You need the output tool outside the macro and have the sheet name on a field on the data for each iteration. 

[frank_sanseri]

If I understand your example, you can use a combination of the Union Tool, Block Until Done Tool, and an Excel Output tool configured to take the tab name from a field in the flow.  I've mocked this up in the attached file to show conceptually how it would work.  

In this case, the queries can produce different data sets, but are still unioned with a common source field to differentiate them later.  This allows the merged data to run through a single Block Until Done Tool, where it is separated by source name, the mismatched columns are removed, and the resulting data sent to the output tool. 

Please take a look and try it in your environment.  If it works, please mark this as the solution. 

[apathetichell]

There are a number of ways to do what you are asking - but if you can explain the architecture of your current workflow (where the queries are output/file/sheet naming conventions - etc) it helps tailor the solution.

Basically each query is assigned a different sheet name and along with a filename this is attached either via append fields/formula tool in batch macro with a replacement or some other mechanism. output data then takes entire path (which means filename|sheet) where each query is a different sheet.

[frank_sanseri]

@olimpio, have you used any of the solutions here? If so please remember to mark it solved for that response.