Outputting a file into the last JE before a certain count of rows

[LiIrving]

Hi - I am going into an ERP integration and attempting to split out the journal entries into the max row upload for the new system. The max row limit is 25,000 including the headers. I know how to break the files into 24,999 rows + headers, but I also need to make sure the last journal entry (JE) doesn't carry to the next file and appear in two files. Is there a way to make the output file stop at the next to last JE before hitting the row limit? This will mean each file will ultimately be less than the row limit to not have JE"s crossover files. Thanks! 

[apathetichell]

I did this by a) keeping my heard block seperate.

b) creating a distinct sheet field using a recordid tool and then dividing the entries by 24,999 (-using floor division) 

non-batch macro way - I'd probably us a batch macro though

c) summarize tool to create the total univrse of distinct sheet.

d) append the distinct sheet universe to the header block.

e) join the header block with the original data on distinct sheet.

f) output data to individual JEs.

for a batch macro i'd filter for distinct sheet and union in my header rows after.

[LiIrving · Accepted solution]

Thank you for your solution; I ended up finding a solution before trying this out. My solution was:

I figured out the solution that worked. 

1) Summarize the data counting the rows per JE

2) Create a running total on the JE count

3)Identify the largest JE

4)Take your max row limit and subtract the max count of JE rows (24,999 - 6624 = 18,375 in my case)

5) Create a formula for the groupings: Ceil ([RunTot_Count]/18375)

6) Join the Group back into the raw JE detail

7) Output the file via CSV and check the "Take file/Table name from Field" box, select "Append Suffix to File/Table Name" and choose your output field

* I also grouped by month so my Step 1 was group by posting period by JE # then count and I had a second formula to get my group after step 5. It was  ToString([postingperiod]) + ' ' + ToString([Group])