community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Exporting to multiple sheets, from multiple tables with different structures

Meteor

I have a workflow that utilizes two different input files (sales transactions and sales adjustments).  The tables can map to each other since they both contain an "invoice line" and "invoice line number" column, however the overall structure beyond those columns is different.

 

I've seen some other posts on here that show how you can write to different worksheets using the values in one of the columns of your table, however, if I have 2 or more tables, with different structures (different columns), is it possible to write them to a single workbook but just have them as different tables?

 

Additionally, is it possible to order the tabs in a particular order? When I've tried the method of using column values to write to different worksheets, the worksheets are placed in alphabetical order. Can I change the order so that Taxable Sales will come AFTER Total Sales for example ("Ta" would come before "To" alphabetically).

 

Finally, I don't know whether this is a bug/glitch, but I've got a table with about 40 columns. I was planning on using the Reporting tools to output my data, but when the combination of the Basic Table tool --> Header tool --> Layout tool --> Render tool, my data does export to an excel file, but when I try and open the file it says "We found a problem with some of the content in <FILE NAME>. Do you want us to try and recover as much as we can?..." The excel file then opens, but it is completely messed up (weird symbols, no actual data, etc.). When I trim back the number of columns, to let's say 6 columns, the data outputs normally, Not sure if it is just a matter of too many columns, or perhaps one particular column causing this. I could try a trial and error scenario, but with 40 columns and the workflow taking a while to run, that might be a bit time consuming. Any suggestions on that would be great.

 

Thank you in advance.

Alteryx Certified Partner

 I've seen some other posts on here that show how you can write to different worksheets using the values in one of the columns of your table, however, if I have 2 or more tables, with different structures (different columns), is it possible to write them to a single workbook but just have them as different tables?


Yes. It is possible to write to two different tables as long as you specify the sheet name in your file output path "%temp%\Filename.xlsx|SheetName". Use the "Overwrite Sheet" option so each sheet is individually managed. If you have different fields in each output, use a Select tool and Output tool for each sheet so you can configure the fields as desired for that sheet.

 


 Additionally, is it possible to order the tabs in a particular order? When I've tried the method of using column values to write to different worksheets, the worksheets are placed in alphabetical order. Can I change the order so that Taxable Sales will come AFTER Total Sales for example ("Ta" would come before "To" alphabetically).


The sheets will always be sorted alphabetically (Output or Render). I typically use a numeric prefix like "1_Total_Sales" and "2_Taxable_Sales".

 


Finally, I don't know whether this is a bug/glitch, but I've got a table with about 40 columns. I was planning on using the Reporting tools to output my data, but when the combination of the Basic Table tool --> Header tool --> Layout tool --> Render tool, my data does export to an excel file, but when I try and open the file it says "We found a problem with some of the content in <FILE NAME>. Do you want us to try and recover as much as we can?..." The excel file then opens, but it is completely messed up (weird symbols, no actual data, etc.). When I trim back the number of columns, to let's say 6 columns, the data outputs normally, Not sure if it is just a matter of too many columns, or perhaps one particular column causing this. I could try a trial and error scenario, but with 40 columns and the workflow taking a while to run, that might be a bit time consuming. Any suggestions on that would be great.


This is a kinda quirky aspect of Excel Rendering: the Paper Size/Orientation settings from the Report Style controls on the Render tool will affect the column size. In scenarios with a large number of fields, try changing the Orientation to Landscape and increasing the paper size (even go Custom Size to increase size beyond 17").

 

 

Hi @ajr2183

 

You can output to separate worksheets and in your preferred order.

What I do and suggest you do as well in your case is:

 

Create a table for Taxable Sales.

  (Configure the table and associated columns as desired.)

Connect a Formula tool, output to a new Output Column called Sheetname.

Enter: 'Taxable Sales'

Create a table for Total Sales.

  (Configure the table and associated columns as desired.)

Connect a Formula tool, output to a new Output Column called Sheetname.

Enter: 'Total Sales'

 

Next:

 

Insert a Union tool.

Connect your 'Taxable Sales' Formula tool output to the Union tool input.

Connect your 'Total Sales' Formula tool output to the Union tool input.

*Notice that each table's 'Sheetname' is listed.

(Set your preferred output order)

 

Next:

 

Insert a Layout tool.

Connect your Union tool output to the Layout tool input.

Layout Mode: Each Group Of Records.

Group By: Sheetname

Layout Configuration: Vertical with Section Breaks (THIS IS A MUST)

Per Section Configuration:

   Data Field: Table

   Section Name: Sheetname

 

Next:

 

Insert a Render tool.

Output to your Excel file.

Report Data:

   Data Field: Layout

   Separator: Insert Section Breaks Between Records

 

Last:

 

Configure your margins, sizes, per row and column styles and rules, etc.

 

Meteor

Wow, I sincerely appreciate the advice from both of you @CharlieS and @HomesickSurferMeteor

 

For the most part, this is working how I need it to. The only other hiccups I am running in to are:

 

  1. I am still having a bit of trouble having the worksheets output in the order I need them to. I've tried adding a numeric value "8_", "9_", "10_", etc. and then placed a sort tool after the union tool (union tool was used to union my two tables, as per 

    @HomesickSurferMeteor 's suggestion), which then sorts ascending. Again, this doesn't appear to output in ascending order. Any thoughts?

  2. I used @CharlieS idea of changing the orientation of my render tool's "Report Style" parameters to Custom size, 50 x 50 so that all of my fields show up. This worked and the data did output correctly, however, since I've unioned two different tables, where one table had 40 columns and the other has about 10 columns, I am running into an issue where the 40 column table will look just fine, but the 10 column table has pretty wide columns. I've tried changing the widths of the 10 column table through the basic table tool by updating each individual columns width under the "Per Column Configuration" section, but that doesn't appear to be working. Any thoughts on that?

Thank you both in advance, I sincerely appreciate the guidance.

 

Best,

Adam

Hi @ajr2183

Perhaps a solution to order your tables and output sheets is to add each table to the union tool in the order in which you want to see them in the output. Delete the connections and manually drag each table output into the union.

For the layout or render, I think there is a option to configure a separator?...I’m on mobile and can’t see right now. Try...

Hi @ajr2183

 

After the Union tool, insert a Layout tool,

 

Group By: Sheetname

Orientation: Vertical with Section Breaks

Layout Width: 100%

Data Field: Table

Section Name: Sheetname

 

Then add Render;

 

Data Field: Layout

Separator: Insert Section Breaks Between Records

Configure your report style

 

1.png2.png

 

Meteor

Ahh.. I was able to figure out the ordering of the sheets... Apparently a sheet labeled "9_Taxable" will show up after a sheet labeled "10_Tax Free"... Makes sense when you think about it, but didn't grasp that right away...

 

In terms of the formatting the columns, I am still having a bit of trouble... I have the same layout and render parameters that you do, however, my Report Style > Paper Size is "Custom Size 50 x 50. I did this because a few of the tables that I unioned have about 40 columns, and apparently when you render that using a Landscape or Letter (8.5" x 11") size, the data comes out completely malformed (random symbols, no data, etc.)... I think perhaps that is what is driving it. For the tables that DO NOT have only 10 or so columns, I've tried adjusting the width in the "Basic Table" tool, but it does feed into that same union, which then feeds into the same Render tool.

 

Not quite sure if there is a fix, but figured I'd ask. 

 

Thank you again for all of your help!

@ajr2183 are you using a Layout tool between your Union and Render?

Meteor

Yup

image.png

 

The Render tool looks like this:

image.png

 

Then the individual tables that DO NOT have 40 columns, I've changed those "Basic Table" individual column widths to "Fixed" and tried to shrink the size to 2 inches, 1 inch and 0.5, but nothing seems to be shrinking the actual column size

 

image.png

@ajr2183 All looks ok.  Perhaps in your tables, set the Table Width to: Percentage: 100 instead of Fixed at 7 inches.

Labels