Insert new row every nth row.

[Charlie_J]

I have 2 columns of data. first column is a string and the second is numbers. i can sometimes have upwards of 3000 rows. However, my final output will be excel files and i can only have 900 lines per file. So every i need to insert a new row at every 900th row and find the total of all the numbers that came before it. Also, the total number of rows i will have will never be an exact multiple of 900. So I'm wondering if there is a way to insert a new row every 900 and dynamically insert a row at the very end of my data?

[BrandonB]

This is one approach

I used a mod function to determine the remainder on the largest number of the group. In my case I set it to 6, but in your case would be 900 or 899 depending on whether you count the total.  Then I used a multi-row tool to create an actual group number because when mod([RecordID], 6) = 0 that means that it is divisible by 6 with no remainder and therefore the last number in the group so the multi row increments to the next group number. Then I feed it into a batch macro that sum totals the values in the group being fed in. Workflow is attached. My other thought was to avoid a macro and use a running total grouping by a given group but then you end up doing some manipulation on getting it under the last row of each group so I figured this was the most straightforward.

[Qiu]

@Charlie_J 
Just a sample. Maybe there is a better way. 😁

[DQAUDIT]

@Charlie- If the objective is to simply output 900 records per Excel file, you may consider an alternate approach.  The Output Data Tool has options that allows you take file/table name from a field within your dataset.  Check this box and use the Change Entire File Path selecting a field that has your generated path for the Excel files.

To Generate the Directory Path per row you will need to do the following steps:

1. Add a Record ID Tool to your workflow
2. Add a Formula Tool to your workflow to generate a string field called Grouping (Expression: CEIL([RECORDID]/900))
3. Add a second field to the Formula tool that generates the directory path (Expression: "C:\Folder\"+[GROUPING]+".xlsx///Data")
4. [Optional] Add a Select Tool to remove the GROUPING Column if you only want two columns in your output data set.

Goodluck!

[TimN]

Hi,

sure if this is any better.  Seems to work...