How can I save a file to a dynamic folder?

[Romela]

I want to save a file, whose filename contains a specific definition, into a directory that follows a convention.

For example: A file starting with 2025_03_Filename.xlsx should be stored in a SharePoint directory named \2025\03_MAR. This should apply to subsequent months as well.

Cheers
Robert

[takbb]

Hi @Romela , if you have the file name as a column in that format, the following String Manipulation would give you the folder name for each:

join("\\",
	replace(substr($file name$,0,7),"_","\\")
	,"_",
	substr("JANFEBMARAPRMAYJUNJULAUGSEPNOVDEC",
		(toInt(substr($file name$,5,2))-1)*3,3)
	,"\\")

This finds the required month out of the substring by using the month number in your filename as a “subscript” or “index” to the string, and returns the 3 characters at that position, so month 1 returns 3 characters starting at index 0 (JAN), month 2 returns 3 chars starting at index 3 (FEB) and so on.

e.g.

I would think you would be doing the file writing within a loop, so you would, for each file, create a String flow variable into which you would join on the additional parts of the sharepoint folder and then use String to Path (Variable) to turn this into a path variable that can then be used within your file output node.

If you need further assistance, or are unsure how to fit that into your workflow, please post back with further questions.

[Romela]

@takbb Thank you so much. ut I generate the file in with a string manipulation. So how can I used it in additional that the system knows the file name and save it in the folder based on the year and month.

[takbb]

Hi @Romela,

I’m slightly confused by your follow up post, as the indication is that in your String Manipulation (Variable) node, you are generating your files with month NAME, but in your original post, you had month NUMBER. As I cannot see your actual data, I don’t know which format you actually have.

I was also previously determining the appropriate folder, based on the file name format that you provided, but now you seem to be indicating that you already have the information in terms of year and month separately as variables, in which case you could also use String Manipulation (Variable) in a similar way to what you have done to build the filename, to build the folder name, so I’m now not sure if that is where you are having difficulty or that was just my misunderstanding.

I don’t have access to a sharepoint account that I can use with KNIME, but I can discuss ideas using Google Drive as an example, as I believe it should be a similar mechanism.

For this, I’m going to assume that you have got to the point where your required folder name is in a variable, having been derived by code similar to what I posted above, or from your own code using String Manipulation (Variable) or similar.

Adapting the previous code that I wrote to work with Google Drive, it became this:

join("/My Drive/",
	replace(substr($file name$,0,7),"_","/")
	,"_",
	substr("JANFEBMARAPRMAYJUNJULAUGSEPNOVDEC",
		(toInt(substr($file name$,5,2))-1)*3,3)
	,"/")

There are a couple of important things to note here. For Google, writing to Google Drive, the initial base part of the folder name becomes /My Drive/ and importantly all of the folder delimiters are forward slash / rather than backslash \. This was important to make Google Drive work. It may also be necessary to do similar for Sharepoint, but as mentioned I cannot access sharepoint to check the exact format required.

Maybe somebody with access to Sharepoint can confirm the required format.

From the image in This post (“Sharepoint folder locations”)

it looks like the required path format for Sharepoint might be:
/Documents/

in which case you’d need something like this:

join("/Documents/",
	replace(substr($file name$,0,7),"_","/")
	,"_",
	substr("JANFEBMARAPRMAYJUNJULAUGSEPNOVDEC",
		(toInt(substr($file name$,5,2))-1)*3,3)
	,"/")

For dynamically generated file names and folders, I would probably use the String Manipulation (Variable) node in conjunction with String to Path (Variable) instead of the Create File/Folder Variables node.

The reason for this is that the Create File/Folder Variables node doesn’t really offer anything to help use with dynamic folder naming. You see, it requires that it be provided with a Path variable for the folder, rather than a String.

This means that you need to have already created the Path dynamically before arriving at this node, which means you would have to use this node IN ADDITION TO the String Manipulation and String to Path variable nodes which we are going to use anyway, and since they can do the entire job themselves, in this case, I don’t see the point of using the dedicated Variables node

A picture is probably easier to understand, so I would build this part of the workflow something like this:

In the above image, the String Manipulation (Variable) node is joining together the dynamically creates variables containing path and filename, to form a “full file path” variable which is then used by the String to Path (Variable) node to create an actual Path variable that can be used by the Excel Writer.

join($${Sfolder name}$$,"/",$${Sfile name}$$)

Of course there is nothing to stop you just doing the creation of the full file path, containing both dynamic folder and file name in a single step, if you don’t actually need to know just the folder name at any point later in the workflow.

The Excel writer is configured to use the Path variable, and is attached to the cloud file system, and hopefully that will work with Sharepoint too.

I hope that is of help, and that you can easily replace the “google drive” parts in the above with the equivalent “Microsoft Sharepoint” parts.

[Romela]

@takbb
Thanks a lot for your help. Yes I changes the month from number to name because the customer change the requirement. But know we have the solution in place and it works fine.

[takbb]

That’s good to hear @Romela . Glad you got what you needed and thank you for marking the solution.