Forum Discussion
Wildcard Matching 2024
Glad you got yours working as it is clearly faster with large data.
Not sure what happened to my last response, but the reason for the errors and mismatches in my regex solution is two-fold.
- There are characters in some of your masks (other than the dot) that have a special meaning in regex and need to be escaped. EG "( ) { } [ ] +"
- I used the word boundary token to ensure that only whole words were returned. But some of your text strings begin or end with a non-Word character, so the word boundary is not at the beginning/end of the string. Would need to know a bit more about your data in order to properly translate this. (A word character is anything in the set "[A-Za-z0-9_]").
Hi Ron,
The main use is to filter paths and/or file names in order to reduce the amount of data loaded into the data model.
An example of file names in a folder:
Report 2023 Qtr-1.xlsx
Report 2023-Qtr2.xlsm
Whatever.pdf
Report 2023 Quarter 3.xlsx
Report 2024 Quarter1.xlsx
Report 2024 Qtr2.xlsx
Data 2024.xlsx
Report 2024 Q3.xlsx
Report 2024 Q4.xlsx
A user could request:
a) Analyze all reports from 2023 => Rep*2023*.xls* =>
Report 2023 Qtr-1.xlsx
Report 2023-Qtr2.xlsm
Report 2023 Quarter 3.xlsx
b) Compare the second quarter of all years => Rep*####*Q*2* =>
Report 2023-Qtr2.xlsm
Report 2024 Qtr2.xlsx
Here is a reduced example from our company, as you see it's the worst:
I:\LOG\Kapazitätsplanung\2013\offene Auftragszeiten 30.05.13 KW22.xlsx
I:\LOG\Kapazitätsplanung\2013\offene Auftragszeiten 31.01.13 KW05.xlsx
I:\LOG\Kapazitätsplanung\2013\offene Auftragszeiten 31.10.13 KW44.xlsx
I:\LOG\Kapazitätsplanung\2014\Kapazitätsplanung-KW24-01.xlsm
I:\LOG\Kapazitätsplanung\2014\Kapazitätsplanung-KW25.xlsm
I:\LOG\Kapazitätsplanung\2014\Kapazitätsplanung-KW26.xlsm
I:\LOG\Kapazitätsplanung\2014\offene Auftragszeiten 27.02.14 KW09.xlsm
I:\LOG\Kapazitätsplanung\2014\offene Auftragszeiten 27.03.14 KW13.xlsm
I:\LOG\Kapazitätsplanung\2014\offene Auftragszeiten 29.01.14 KW05.xlsx
I:\LOG\Kapazitätsplanung\2015\Fertigungsliste ab 07.04.15.xlsx
I:\LOG\Kapazitätsplanung\2015\Gesamt VK-Aufträge 2014.xlsx
I:\LOG\Kapazitätsplanung\2015\Kapazitätsplanung-KW11.xlsm
I:\LOG\Kapazitätsplanung\2015\Kapazitätsplanung-KW12.xlsm
I:\LOG\Kapazitätsplanung\2015\Kapazitätsplanung-KW12_alt.xlsm
I:\LOG\Kapazitätsplanung\2015\Kapazitätsplanung-KW13.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-17.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-18 - neu.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-18.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-20.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-22.xlsm
I:\LOG\Kapazitätsplanung\2024\Kapazitätsplanung-KW24-24.xlsm
I:\LOG\Kapazitätsplanung\2024\Produktion Kapazitätsgrobplanung 2024.xlsx
I:\LOG\Kapazitätsplanung\2024\Produktion Kapazitätsgrobplanung 35000000.xlsx
I have 1604 files in just that folder... welcome to my world of crap data. 😆
We produce systems for the food industry worldwide, and almost every customer has different requirements. If you have a new request, you can remember "yes, we've done something like that for customer XY before." Where is the data for it?
And in most companies it's not much different, you start somehow and then over time you develop a system that grows.
Andreas.
That explains your problem better.
A suggestion: Instead of relying on the user to construct a valid wild-card "like" input, why not have them submit the parameters utilizing a User Form. Then you'd get valid data input and your filtering code would probably be much simpler.
That means I would have to split the process into PQ.
I would first have to read in all the files with PQ, then call up the user form, then filter the data with VBA and LIKE, write the result into a table, read this into PQ and now I can continue with the actual data import.
IMHO this is quite a big detour and fraught with further difficulties. Thanks, but no thanks.
Let’s stick to the topic: Wildcard comparison.During my research I often read: Use RegEx. But I have never seen how to filter real-life data with RegEx.
Don't get me wrong, you can make amazing comparisons with RegEx that you would otherwise have to write an entire parser for, very impressive.
The big problem with RegEx is the syntax, it looks like a rattlesnake has walked across the screen, no normal user understands it and even for me as an experienced programmer it is too complicated. A wildcard comparison, on the other hand, is simple and intuitive to use.
And it seems as it is too difficult to do a wildcard comparison with file paths using RegEx for you too. (Don’t get me wrong, I didn’t manage it either). Or is there an update that I missed?
Andreas.
a. I stopped working on the regex when you reported you had your routine working, and also that the regex was considerably slower. But if your routine is not returning what you need, I'll be happy to finish up the regex now that I have more information.
b. I didn't realize you would have to read in all the files if you chose to use a user form. My idea of that was just to replace whatever you are using now to obtain the wild card string from the user, with a form that had a more structured input.