Multiple SQL Queries into 1 excel file with sheets

Question

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?

frank_sanseri · Answer

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

apathetichell · Answer

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 · Answer

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.

SampleQueryOutputToDiffSheets.yxzp