Excel to JSON: Multilayer nested arrays/objects

Allright @TheController , this is the route I took:


  • Since we’re dealing with rows and columns that need to stay separated, I initiated a chuck loop, making the WF process each record individually.

  • Make a split between DI and non DI information (similar to the first version). For the DI, I initiate an Interval Loop between 2 and 5. This corresponds to your DI numbers.

  • Due to the common column name issue, I process each DI individually. As such, I need to dynamically filter for the DI that is being processed to later on create the JSON. This can be done with join("*DI") + string($${Iloop_value}$$). Convert it to a flow variable and pass that to a Column Filter node. Make sure the variable is applied properly.


  • It’s then possible to remove the DI from the column name when using [ ](DI)[0-9]+ in a Column Rename (Regex) node.

  • Create the JSON as earlier done and, after all DI’s have been processed, combine them with a JSON Row Combiner.

  • Append that again to the non DI information and create the final JSON (use unnamed root element again).

  • Exit the outer Chunk loop. If you eventually need to do the POST request, you can technically already put that after the last JSON creation before the exit the outer loop.

  • Final result is two full JSON’s.

Note that I didn’t cater for the possibility that a DI between 2 and 5 is not present. If could be that the workflow crashes or outputs unwanted results like an empty JSON, but I will leave that up to you to catch :wink:

See WF:
Excel to JSON Multilayer nested arrays objects V2.knwf (75.6 KB)

If this helped please mark the post as solution so that other KNIME users can also benefit from this in the future.