Recently I had a requirement to poll an SFTP server and when files are produced we needed to archive them to a sharepoint site for business users. It was a pretty straightforward requirement and the only bit of logic we needed to handle was that the file name of the file in the FTP folder we would poll would be parsed and split so that the first part would indicate the folder in sharepoint and the 2nd part of the file name would be the file name in sharepoint.

FTP File Name = [CustomerID]_[DocumentID].pfd

CustomerID = Folder

Document ID = filename

The bit that seemed to be more complicated than it should be was the create if not exists for the folders in sharepoint and the overwrite of a file if it already exists but got updated by the source system.

There are quite a few actions on the SharePoint connector but none that seem obvious to cover “Does the file exist” or “Does the folder exist”

I spent a while messing around with list files, list folders, folder properties meta data, etc and all of them seemed frustratingly difficult to just check if the file/folder exists and then create or update.

In the end it turned out there is a really way to handle this scenario but there doesnt seem to be anything out there which covers clearly so I wanted to share what I ended up doing to help others in the future who have the same problem.

Create Folder if not Exists

This one is actually a lot easier than it sounds. If you just use the “Create new folder” action it will automatically create it if it doesnt exist and if it does exist it will not throw an error. What you do need however is the path to the folder for later. This action will return a response body but it is not supported with designer properties but you can use the parse json shape to get the info.

Below you can see I provide the sharepoint address, doc library and folder path

You can see in the response body the {FullPath} property will provide the full path you want for dynamically creating the file in the next step. For some reason the create file needs the full path where as create folder wants a document library then a relative path within that library. I dont really know why these are different.

I found that If I use a parse json shape and pass in the body from the create folder then you can parse these properties. I found that the design properties from the create folder action didnt show up automatically but you can use the expression @body(‘Create_new_folder’)[‘body’]

The json schema I used is below which gives me some useful properties for the folder.

{
  "type": "object",
  "properties": {
    "ItemInternalId": {
      "type": "string"
    },
    "ID": {
      "type": "integer"
    },
    "Title": {
      "type": "string"
    },
    "{Link}": {
      "type": "string"
    },
    "{Name}": {
      "type": "string"
    },
    "{FilenameWithExtension}": {
      "type": "string"
    },
    "{Path}": {
      "type": "string"
    },
    "{FullPath}": {
      "type": "string"
    },
    "@odata.etag": {
      "type": "string"
    }
  }
}

If you know the path you need the folder to be at, using Create folder seems to be a better way than just searching for it and you can just use the output. If the folder doesnt exist its created and if it does exist you just get the properties back.

Create or Update File

For the creation of the file I will use the create file action. I will pass in the {FullPath} property form the parse json as the folder path. This gives me the right path from the folder in the previous step as shown below.

At this point the create file will work if the file doesnt exist but will fail if it does already exist.

You can now turn off chunking on the settings of the create file.

By turning off Allow Chunking the change in behaviour means that the File is overwritten if it already exists and also if it does not already exist the file is created.

I think there could be some constraints for large files here but for my scenario this doesnt matter.

In summary I was able to get rid of all of this complicated logic check if it exists and then trying to use different create or update scenarios. I end up with just 3 actions:

  • Create Folder
  • Parse Folder Result
  • Create File

This handles the exists/not exists without needing to over complicate your logic app. The final logic app steps look like this:

Note this will also work for Power Automate too.

 

Buy Me A Coffee