How to Find Files in OneDrive That Match a Naming Pattern using Power Automate

A user has the following files located in OneDrive. How can we find these files?

Køretøjsliste for 4874349 for dato 31.01.2020 - 01.05.2020.xls
Køretøjsliste for 4874351 for dato 30.02.2021 - 03.10.2021.xls
Køretøjsliste for 4874352 for dato 29.03.2022 - 09.06.2022.xls
Køretøjsliste for 4874353 for dato 28.04.2022 - 11.09.2022.xls
Køretøjsliste for 4874354 for dato 27.05.2022 - 15.12.2022.xls
Køretøjsliste for 4874355 for dato 26.06.2023 - 22.07.2023.xls
Køretøjsliste for 4874356 for dato 25.07.2022 - 27.08.2022.xls
Køretøjsliste for 4874357 for dato 24.09.2029 - 28.12.2029.xls
Køretøjsliste for 4874358 for dato 11.10.2015 - 29.10.2015.xls
Køretøjsliste for 4874359 for dato 04.11.2024 - 30.11.2025.xls
Køretøjsliste for 4874360 for dato 01.12.2028 - 31.12.2029.xls

The filenames have a specific format:

The filenames start with “Køretøjsliste for”, followed by seven digits, followed by “for dato”, followed by a date in the format of “dd.mm.yyyy”, a hyphen, another date in the same format, and ending with the file extension “.xls”.

Use Pattern Matching to Find these files

We can use the following pattern (known as a regular expression, or regex for short) to match the above filename pattern and find these files in OneDrive:

Køretøjsliste for \d{7} for dato \d{2}.\d{2}.\d{4} - \d{2}.\d{2}.\d{4}.xls

We use the above expression in a search query with the Find files in a folder path action. We need to specify the File Search Mode as Pattern:

RegEx Expression Generator

Here is the link to a RegEx expression generator I used to create and test the file matching pattern: https://regex101.com/r/cW04fu/1

This regular expression is designed to match the specific pattern of the filename.

This regular expression will match a file name that starts with “Køretøjsliste for “, followed by seven digits, followed by ” for dato “, followed by a date in the format of “dd.mm.yyyy”, a hyphen, another date in the same format, and ending with the file extension “.xls”.

Let’s break it down:

• Køretøjsliste for: This is a literal string that will match the exact text "Køretøjsliste for " in the file name.
• \d{7}: This will match any seven consecutive digits in the file name.
• for dato: This is a literal string that will match the exact text " for dato " in the file name.
• \d{2}.\d{2}.\d{4}: This will match a date in the format of two digits for the day, two digits for the month, and four digits for the year separated by periods (e.g. 13.05.2023).
• -: This will match a hyphen in the file name.
• \d{2}.\d{2}.\d{4}: This will match a second date in the same format as the first one.

The final part of the regular expression, .xls, matches the file extension of the file, which in this case is “.xls”.

Other than one or two minor exceptions, Power Automate has no built-in support for Regular Expressions.

Ask ChatGPT to generate the Pattern Matching Expression

We can also ask ChatGPT to generate the regular expression for us. I supplied a list of sample filenames and ChatGPT produced the following expression as we see below:

Create a regular expression to match the following filename examples:

Køretøjsliste for 4874349 for dato 31.01.2020 - 01.05.2020.xls
Køretøjsliste for 4874351 for dato 30.02.2021 - 03.10.2021.xls
Køretøjsliste for 4874352 for dato 29.03.2022 - 09.06.2022.xls
Køretøjsliste for 4874353 for dato 28.04.2022 - 11.09.2022.xls
Køretøjsliste for 4874354 for dato 27.05.2022 - 15.12.2022.xls
Køretøjsliste for 4874355 for dato 26.06.2023 - 22.07.2023.xls
Køretøjsliste for 4874356 for dato 25.07.2022 - 27.08.2022.xls
Køretøjsliste for 4874357 for dato 24.09.2029 - 28.12.2029.xls
Køretøjsliste for 4874358 for dato 11.10.2015 - 29.10.2015.xls
Køretøjsliste for 4874359 for dato 04.11.2024 - 30.11.2025.xls
Køretøjsliste for 4874360 for dato 01.12.2028 - 31.12.2029.xls

The expression that ChatGPT generated was slightly different to mine but works nonetheless:

^Køretøjsliste for \d+ for dato \d{2}\.\d{2}\.\d{4} - \d{2}\.\d{2}\.\d{4}\.xls$

Explanation of the regular expression:

• ^ asserts the start of the string.
• Køretøjsliste for matches the literal text “Køretøjsliste for” in the beginning.
• \d+ matches one or more digits.
• for dato matches the literal text ” for dato “.
• \d{2}\.\d{2}\.\d{4} matches a date in the format “dd.mm.yyyy”, where \d{2} matches two digits and \. matches a dot.
• - matches the literal text ” – “.
• \d{2}\.\d{2}\.\d{4} matches a date in the format “dd.mm.yyyy”.
• \.xls matches the extension “.xls”.
• $ asserts the end of the string.

Example Runtime Output of the “Find files in folder by path” action

Here is a OneDrive search showing filenames starting with Køretøjsliste:

“Find files in folder by path” action with the Pattern matching selected. The Select action extracts (selects) just the filenames from the search results:

Runtime output:

The results of the Select action which extracts (selects) just the filenames from the search results:

Notes

1. How to use Regex in Power Automate by Paul Murana. “there is no Regular Expression support within the standard Power Automate Actions, but there is regex support built into JavaScript, which can be accessed via Office Scripts in Excel Online.”
2. Learn the Basics of Regular Expressions
3. Regular Expression Language – Quick Reference