Split output to multiple worksheets in excel

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?

Output

Accepted answers

All comments

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.

Use a field info tool to get the list of field names.
Add a record ID to the list of field names
Add a formula that add a field call NewName with the formula tonumber(RecordID)
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
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
Use a Dynamic Rename set to take field names from right input rows after each Dynamic Select to go from NewName back to Name.
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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703