Insert new row every nth row.

Question

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?

TimN · Answer

Hi,

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

Add_Every_900.yxmd

JBLove · Answer

@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:

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

Goodluck!

Qiu · Answer

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

1117-Charlie_J.yxmd