This is perhaps one of the more convoluted things I’ve had to unravel with Knime, but I’m going to post it in case someone else runs into the same scenario.
This workflow essentially generates a dynamic folder name and concatenates that to the root path generated by the Variable Creator node. They are merged and converted to a data frame so as columns they can be passed to the String Manipulation node and concatenated into a URI formatted string.
Where this gets interesting is the path conversion. Google uses a URI format (“/”) and the String to Path variable, by default, results in a windows format (“\”). So the way to get around the default behavior is to change the file system to Mountpoint, which doesn’t change the URI format and outputs as type path. String to Path doesn’t output a path flow variable either, so it’s not possible to simply connect it directly to the Excel Reader node.
That little gem would have saved me a lot of time if it was documented somewhere. A nice feature would be the option to choose between URI and Windows output format to make the node more user-friendly.
Finally, the path has to be passed to a Table Row to Variable node so it can be passed as a incoming flow variable to the Excel Reader node, which only accepts a flow variable of type path. Then the dynamic path works like a charm.
Python (PurePath), Java (ParsedPath, PathFactory), and numerous other nodes could not solve the path conversion problem between URI and Windows, but I’d love to hear other approaches if they exist.