Today something that looks simple, but it’s a bit tricker than it sounds. We’re going to fetch files from a folder but only from a specific type, for example, all Excel files in a folder. So the idea is to have a template that we can invoke to get all files from a type in a folder and parse them accordingly.
Get “Media Type” of a file
Get the file’s “Media Type” to filter all files of a type in a folder for example.
Finally, here’s the solution with both the template above and the example in this article:
Solution with the “Get Media Type of a file” and “Filter files of a type”
Here’s the solution with a working example of how to fetch all the files of a type in a folder, using the “Get media type” template above.
There are a few things to understand, so let’s get started.
The setup
The template will use a SharePoint Document Library, but you can use OneDrive or even Microsoft Teams. It works the same way across all of them and what’s important is the strategy.
So we’re going to do the following:
- First, fetch all files from a folder.
- Filter by the type that we want
That’s it, but the second step has a tricky part—the type itself.
It’s easy to fetch files in a folder. First, we can use SharePoint’s List folder action to return all the files in a folder. After that, we have the “MediaType” field that will return the file type, and we can filter by that. But here’s what it returns:
Let’s expand it a bit (removed the properties that are not interesting for now):
[
{
"Name": "Document.docx",
"DisplayName": "Document.docx",
"Path": "/Shared Documents/Test Files/Document.docx",
"Size": 17138,
"MediaType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
"IsFolder": false
},
{
"Name": "2021-11-01 10.22.51.gif",
"DisplayName": "2021-11-01 10.22.51.gif",
"Path": "/Shared Documents/Test Files/2021-11-01 10.22.51.gif",
"Size": 171330,
"MediaType": "image/gif",
"IsFolder": false
},
{
"Name": "Book.xlsx",
"DisplayName": "Book.xlsx",
"Path": "/Shared Documents/Test Files/Book.xlsx",
"Size": 12972,
"MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"IsFolder": false
}
]
As you can see above, we understand what we have a word, gif, and excel files, respectively, but look at the “MediaType”. The Excel, for example, is “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”. Quite an ugly representation, right?
There’s not much that we can do about the types since they are part of a standard definition, but we can hide all the ugliness behind a Flow that takes care of all of this. So let’s build it.
The template
So now that we know what to look for let’s build a template that returns us “something” that we can filter on.
Also, if you want to submit a template with more “MediaTypes”, I’m more than happy to feature you.
Here’s the template. First, we define a static list of items to choose from.
Then, depending on the type, we’ll return the “code”.
It’s pretty simple, but you need to know what to insert.
Fetching the rows
Now that we have a template to do all the heavy lifting for us, here’s how to filter the files in the folder.
We fetch the files with SharePoint’s List folder action and then filter with the value returned from the Flow before. Here’s what we get:
[
{
"Name": "Book.xlsx",
"DisplayName": "Book.xlsx",
"Path": "/Shared Documents/Test Files/Book.xlsx",
"Size": 12972,
"MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"IsFolder": false
}
]
We see that we only get Excel files, so it’s easier to now parse the files.
Final thoughts
The Flow to fetch all files from a type in a folder is simple once we understand the “MediaType” part. Of course, these conventions don’t make things simple, but the internet was built on patterns like this.
The “Get MediaType” template will help deal with more strange cases, and as always, we separate things that are useful in another Flow to re-use them in other Flows.
Photo by Christina Rumpf on Unsplash