A client had asked me to develop a method of automatically segmenting (chunking) our data by implementing a row limit.
It was now up to me to find a solution, and after gaining an understanding of the ways of which you can chunk data within an .xlsx output I was now getting down to trying to implement this knowledge.
And with that may I introduce the ‘Chunk Excel Output‘ macro.
As with all applications and macro’s that I build, I will always build out a traditional workflow first. I do this, a) to ensure that the idea is actually possible, and b) to avoid the complications that come in when introducing interface tools into the process. By doing the latter first, we may never get to a stage where we achieve the goal of point a.
My initial idea was to use the ‘Change Entire File Path’, which I knew would give the users flexibility to either output the chunks to different workbooks, or sheets, as desired.
The challenge here was, how do I acknowledge when a defined row limit was hit?
Firstly, something I knew I needed to be able to do was to acknowledge the row of each line, so the starting point was a Record ID tool. I then thought that I could use a multi-row formula tool to essentially create a condition which asks the question, does the record ID of the current line, meet the criteria of the current file (the current file being an incremental value which starts at 1).
This develops into a formula as (where the row limit is defined as 1000):
if [RecordID] = 1 then 1 elseif [RecordID]<=([Row-1:File]*1000) then [Row-1:File] else [Row-1:File]+1 endif
//Here we are defining whether the current row is the 1st row in the file, if it is then we will give it the [File] value of 1 (i.e. it is within our first chunk)
IF ISNULL([Row-1:File]) THEN 1
//If the record is not the first row in the file then determine whether the Record ID of the current row is less than the row limit, multiplied by the chunk number
//If the previous statement returns TRUE, then we increment the chunk value, if not we keep the chunk value the same as the previous row
ELSE [Row-1:File]+1 ENDIF
We can then use a formula tool to append this value to a file path string…
And now we have a different file path for each ‘chunk’.
And then as we use the ‘Change Entire File Path’ from our ‘File Output’ field, and run the workflow our records will now output accordingly.
And now that we have a method too create chunking by a row limit we can now bring our user input. In the case of this app, we simply allow the user to select their file path and chunking type, and manipulate the URL string accordingly, whilst we also allow them to overwrite the default value within the multi-row formula tool that gives the row limit.
I don’t want to go too much in detail in regards to the building of the app, but there are some great blogs out their in regards to the interface designer tools, including ‘One Step Closer to Alteryx Nirvana – Building Macros and Apps‘ by Michael Mixon, whilst Naledi Hollbruegge has posted a number of blogs that focus on specific interface tool on The Data School blog.
If you wish to see the detail of how the final macro was built then it is available to download.