How do I add fill colors and borders to my Excel data output? Render seems remove it

[alteryxisconfusing]

I read this can be done using the table and render tools and it seems to work, but my issue is that it keeps overwriting my other sheets from my "data output" tool. 

My current flow looks like this:

Data Input = Workbook1.xlsx

Workbook1.xlsx

Sheet1WB1

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet1WB2

Block until done > Data Output (Results.xlsx)

Then

Data Input = Workbook2.xlsx

Workbook2.xlsx

Sheet2WB2

Block until done > Data Output (Results.xlsx)

This results in the following Output

Results.xlsx

Sheet1WB1

Sheet1WB2

Sheet2WB2

a single Excel workbook with multiple sheets (desired result).

What I am trying to add:

I would like to add formatting to "Sheet1WB2", but when I use the table and render tools the entire output file (Results.xlsx) gets overwritten and only shows Sheet1WB2 with the formatting. 

[alteryxisconfusing]

Hi,

Here's the raw input data I'm trying to use with the workflow and the desired output pivot table I'm trying to get.

Basically I swapped the "Workbook1.xlsx" in your workflow and I added a "cross tab" tool right before the "table" and "union" tool, but it doesn't go into the output file correctly. 

[gautiergodard]

Hey @alteryxisconfusing 

See attached for one way you could achieve this!

Please mark all posts containing solution on this thread as resolved in order to make it easier for the next person to find answer!

Hope this helps

[alteryxisconfusing]

Hi,

Here's the input of the raw data I"m using and the desired output. 

I basically changed the input file of workbook1 in the workflow you put together and then I added a "cross tab" tool before the original "table" tool that ultimately leads to the original "union" tool. I'm having trouble with the output since it keeps throwing an error and the format corrupts.

[gautiergodard]

@alteryxisconfusing answer was provided above, reattaching here!

[alteryxisconfusing]

Whoops! Didn't see the page 2 on the comments ha

thank you so much for the help so far! Will give this a shot right now.

Will the pivot test still have the logic to group by "Tab"? I still want it to go in as an outputted Excel sheet.

[gautiergodard]

The approach can be leveraged. You can always create a new "Tab" field using a formula tool in order to create a tab based on values which are not present in your data. 

Please mark all posts on this thread containing solution as accepted in order to help others answer similar issues in the future!

[alteryxisconfusing]

When I try to leverage the cross tab into the blend.yxmd the outputted Excel says, "we found a problem with some content....do you want us to recover as much as we can"?

Then when I go to the specific tab the content is there, but some of it is not legible or sensible. Basically the mix of coss tab + formatting seems to be corrupting the entire workflow. 

[alteryxisconfusing]

Hm so I cut down the size of the text in my raw data and it seems to work if it's only a small set of text. 

I'm wondering if perhaps I need to increase the "field size" to be larger than 2048?

[alteryxisconfusing]

Hm tried increasing the field size and no luck....my max character length is only 187 and max rows are 2186 (input file). 

Looks great if I cut it to a size of 30 rows (input file)

[gautiergodard]

@alteryxisconfusing Could you share the full workflow? Suspect there may be inconsistencies with how the reporting tools were formatted.