Split output to multiple worksheets in excel

[cowannbell]

I have a output that has a great deal of columns that is too much for one excel workbook.  I know that I can use csv but is there a way to force it to multiple worksheets in excel?

The columns could change every time it is ran, so I can't split it out by column name. I'm thinking force it to a new columns after a certain number of columns?

[danilang]

Hi @cowannbell 

The excel limit is 16384 columns.  Do you have that many columns in the input? Or is the error being caused by the number of rows(1048576)?  If it's rows, then you can add a RecordID and then a formula tool to create a new sheet name after every 1M rows.  Then in your output tool have the sheetname taken from the sheet name field.

If you really do have more than 16384 columns what's the point of outputting to excel?  No one's going to open a spreadsheet that big.

Dan

[cowannbell]

It really is that many columns.  It's not the rows.  That is why I need to split this out and yes they do intend to open it.

[aatalai]

Use the output tool and put take field directory from field

[cowannbell]

 take field directory from field?

[aatalai]

sorry file path

[cowannbell]

How would that help in this situation?

[HomesickSurfer]

Hi @cowannbell 

If I had more time, I'd attempt to provide a working flow.

At first review, I'm thinking you can make use of transpose, assign record ID, create tiles with the Tile tool (Method:Manual, Numeric Column: RecordID), assign incremental tile cutoff's, create a new [Sheetname] field equal to the Tile_Num...then somehow crosstab and then create a [Fullpath] field equal to path, filename, extension and [Sheetname]...the config Output tool to Change Entire path using [Fullpath]

[danilang]

Hi @cowannbell 

Crosstabs have a semi-firm limit of 1000 columns after which they complain...a lot.  There's also the column reordering problem which may or may not be an issue for you.

Here's an algorithm that avoids all the transpose/crosstab mechanics and can work in a reasonable time. 

1. Use a field info tool to get the list of field names.  
2. Add a record ID to the list of field names
3. Add a formula that add a field call NewName with the formula tonumber(RecordID)
4. Use a Dynamic Rename set to take field names from right input rows on your input data to rename your fields from Name to NewName
5. Use Dynamic Select tools in parallel to select sets of columns fieldnames from 1 to 10000, 10001 to 20000, etc., as  required. i.e. ToNumber(_FieldName_)>=1 and ToNumber(_FieldName_)<=10000
6. Use a Dynamic Rename set to take field names from right input rows after each Dynamic Select to go from NewName back to Name.
7. Add an output data tool after each Dynamic Rename to output the data to individual sheets in your workbook.  

There are other things to possibly deal with such as key fields repeated on every sheet if required and synchronizing the output tools since you're writing to mulitple sheets in the same workbook.  You can deal with the former by using the existing field name as the NewField for all your key fields and explicitly selecting those in the Dynamic Select tools.  The Parallel Block until done form the CReW macro pack can help with the synchronization issues.  

Dan

[danilang]

@cowannbell 

Here's a sample of the algorithm in action, including handling key fields

 

BTW:  Thanks for the brain stretching question!    

Dan