Sample Data and Basic table output/export in one single Excel file, in different sheets

[stefaniadurdan]

Hello, everyone,

I am seeking for your guidance in regards to exporting some data in Excel, in two different sheets of the same file.

I am starting from one Input file and then there are two ramifications of this:

1. There is a Sample tool on first 3 rows (just to give an example - the thing is that there is a data manipulation)

2. Summarize the results and create a Basic table to show it in a nice format

Now I want each of the above to be in different sheets, but in the same Excel file, 1 - to be in Sheet 1 and 2 - to be in Sheet 2.

I managed to find out how to export a Basic Table in Excel (as I have seen many replies where this would be not possible) - using a Render tool, this will be exported in a temporary Excel file.

And it is being exported like this:

But the challenge is I do not know how to combine with the other data for them to be in the same Excel file.

In order to output two different datasets in same Excel file, I have seen there should be a column created with "Sheet name" and in the Output configuration I would take file/table name from this column and unticking the box of "Keep Field in Output". This works for normal datasets, but not when containing a Basic Table.

This is how I tried.

But the export looks like this:

Sheet 1 (which is fine, this is how it should look like):

But Sheet 2 does not show the exact table that I want, like previously shown:

Is there a way I could do it?

I am also attaching the Alteryx workflow.

Thank you in advance!

[Qiu]

@stefaniadurdan 
I think we should not mix the data and rendered table together.

I fixed the work a bit for your reference.
And since empty columns will be generated, I would suggest to have batch macro to cleanse the data before outputting.

[Kamran1991]

Hi @stefaniadurdan ,

Please use 'Report Text ' tool  to enter additional textual data and then 'Join' incoming data from 2 'reporting' tools.

Hope this will work.

Thanks,

Kamran

[Kamran1991]

Additionally, you need 'Render' tool to export the output.

[grazitti_sapna · Accepted solution]

@stefaniadurdan , You need to use render tool instead of Output  Data tool. Please find the updated workflow and accept my solution if this was what you needed. 

[stefaniadurdan]

Hello! Thanks for sharing! This is almost complete, I have figured it out with a colleague we need also a Layout tool before the Render tool. Otherwise, the table will not be properly exported in Excel.

Other than that, your solution is the nearest to the solution. Thank you very much!

I have amended as below:

[grazitti_sapna]

@stefaniadurdan , Definitely, its always better to use layout before render when you want to use multiple outputs it will also be useful if you want to assign self-defined names to the sheets in excel.

[johnaberham]

Throwing an error for me