Parsing File Paths from Access Tables

Hi! I need a bit of help parsing file names in VBA. I would rather use Instr rather than regular expressions. Thank you in advance for any advice you may be able to offer.

If a file name begins with a numeric, there would be no prefix <NULL>, and if a file name has no numerics, the entire file name would be the prefix and the suffix would be <NULL>. Extension should be truncated.

• The source data is in the "Files" table
• Result should display Files from the Files table, plus FolderPath (without file name) and FileName (without file path) for all files
• For those files where the filename has both letters and numbers, there should also be two other fields: Prefix (introductory letters before the first number) and Suffix (following number/letter combination)

Sample output:

Files
FolderPath
FileName
Prefix
Suffix

C:\WINDOWS\Help\AGT0406.HLP
C:\WINDOWS\Help\
AGT0406.HLP
AGT
0406

C:\WINDOWS\Help\iisHelp\common\400.htm
C:\WINDOWS\Help\iisHelp\common\
400.htm
400

C:\WINDOWS\Help\masked98.chi
C:\WINDOWS\Help\
masked98.chi
masked
98

C:\WINDOWS\Help\ACCESS.HLP
C:\WINDOWS\Help\
ACCESS.HLP
ACCESS

hello alecdba, have you already extracted the file names from the path?
How did you "obtain" the paths?
The reason that I ask is that I have a "Document Management" database that does exactly that using the GetFileName function as the path is imported.
It would be one less step that you would need to do in the parsing process.
Do you also want the File Type stored in the Table, i.e. HLP, htm, chi etc?

I can create the VB code for you but I need to know where/how you want to do the parsing.
On a form with a command button?
On a form using the form's "On Curent" Event Procedure?
During the File Path's Import?

Quote:

---

Originally Posted by OBP

hello alecdba, have you already extracted the file names from the path?
How did you "obtain" the paths?
The reason that I ask is that I have a "Document Management" database that does exactly that using the GetFileName function as the path is imported.
It would be one less step that you would need to do in the parsing process.
Do you also want the File Type stored in the Table, i.e. HLP, htm, chi etc?

I can create the VB code for you but I need to know where/how you want to do the parsing.
On a form with a command button?
On a form using the form's "On Curent" Event Procedure?
During the File Path's Import?

---

Hi OBP,

Thanks for your response. The file paths are stored in a table "Files". The preferred method is to return the results as query results. Can we use the Instr function as part of an access Select query?

aleccdba, no you can't use Instr because the first character that you need to find is the "\" to seperate the file name from the full path. Instr finds the first incidence of the "\" but not the subsequent ones.
This is much easier to do in VBA, hence the question about a form etc.

Once the VBA has put the File Name, Path, Suffix and Prefix in the table you can the use a query to display them.
Or you can use a form and display them as they are parsed in "Unbound" fields and not save them in the table.

Quote:

---

Originally Posted by OBP

aleccdba, no you can't use Instr because the first character that you need to find is the "\" to seperate the file name from the full path. Instr finds the first incidence of the "\" but not the subsequent ones.
This is much easier to do in VBA, hence the question about a form etc.

---

Looks like it would be much easier in VBA. The file names are indeed links to documents in a document management system. Can GetFileName retrieve the path only? That also leaves the issue of retrieving the prefix and suffix I described, as well. I'm not really picky about how the results are displayed. A command button on a report would be just fine.

Again, these file names are indeed stored in an Access table an not imported.

I am VERY grateful for your responses!!

This is a first go at doing what you want from your description.
When you step through the records of the Form it parses the Full Path in to the path (filepath), the filename (document name), prefix and suffix.
I used the table from my Document Management database, hence the incorrect names.
Have a look and let me know if it basically does what you want.

Quote:

---

Originally Posted by OBP

This is a first go at doing what you want from your description.
When you step through the records of the Form it parses the Full Path in to the path (filepath), the filename (document name), prefix and suffix.
I used the table from my Document Management database, hence the incorrect names.
Have a look and let me know if it basically does what you want.

---

OBP, this is excellent indeed. I have to run for the time being, but the construct is exactly what I need, and I will adapt it to my application and get back a bit later today (evening your time). By then I suspect we will be able to mark it "Solved"!

Thanks again so much. :bow: :beerchug:

Edit: By the way, I think that if the file name is numeric or if the first character of the file name is numeric (which would make for a NULL prefix) it does not write the suffix to the suffix field, it drops out and leaves it NULL. I believe this is easily fixed, however.

Why not use InstrRev?

That is if you are using 2000 or later.

You could also look at using Dir if these files actually exist.

Here's a stab at the first 2

SELECT FilesTable.Files, Left([Files],InstrRev([Files],"\")) AS FolderPath, Mid([Files],InstrRev([Files],"\")+1) AS FileName
FROM FilesTable;

Norie, thanks for the heads up on that function.

OBP

No problem.:)

I think we'll need to see if the OP is using 2000 or later though.

PS I recall writing my own version of InstrRev when I only had Access 97 but had Excel 2000.

Don't know if I've still got the code, but if I find it I'll post it.

Quote:

---

Originally Posted by Norie

Why not use InstrRev?

That is if you are using 2000 or later.

You could also look at using Dir if these files actually exist.

Here's a stab at the first 2

SELECT FilesTable.Files, Left([Files],InstrRev([Files],"\")) AS FolderPath, Mid([Files],InstrRev([Files],"\")+1) AS FileName
FROM FilesTable;

---

Thanks, Norie! Of course, the first part works wonderfully to parse the path, I just have to perfect the syntax for the prefix and suffix.

Again, many thanks to both of you. :friends:

alecdba, this version uses Norie's Instrrev function in the VB code instead of the for/next loops.
It also corrects the suffix problem.

Norie, is it possible to do the second part of the requirement , i.e. splitting the name in to Numeric and Text in a Query/SQL without using a VB Function?

Quote:

---

Originally Posted by OBP

alecdba, this version uses Norie's Instrrev function in the VB code instead of the for/next loops.
It also corrects the suffix problem.

Norie, is it possible to do the second part of the requirement , i.e. splitting the name in to Numeric and Text in a Query/SQL without using a VB Function?

---

OBP, I believe this is the same file you uploaded for the previous solution.

Thanks,

Alec

Sorry

OBP, I notice that your Files table is populated already with path, prefix, and suffix, and that files is the record source for the form. How did you populate the Files table with those items?

alec, in the VBA code where you see
Me.fieldname = something
the value has been passed to the field in the Form and hence the Table.
This will continue to do this for every record in the form as you move to it, so ideally the VBA code should have an
If not isnull(Me.Filename) then exit sub
where Filename is the name of your Form Field that holds the "parsed" File name.
So if the file name field is not Null (Blank) then it won't bother to do the parsing again.

Quote:

---

Originally Posted by OBP

alec, in the VBA code where you see
Me.fieldname = something
the value has been passed to the field in the Form and hence the Table.
This will continue to do this for every record in the form as you move to it, so ideally the VBA code should have an
If not isnull(Me.Filename) then exit sub
where Filename is the name of your Form Field that holds the "parsed" File name.
So if the file name field is not Null (Blank) then it won't bother to do the parsing again.

---

Thanks OBP. I guess I figured out that most basic of questions before the site crashed yesterday. :doh:

alec, in fact the code could also be cut down by not having any Variables assigned values and just assign them straight to the Fields. I did it that way so that I could check each "phase" of the coding with message boxes to ensure that the data parsing was correct.
If you need any more help just let me know.

OBP

I don't know if the rest of the stuff can be done without VBA.

I think returning the filename without the extension would be easy using text functions but the other part regarding the prefix/suffix is a little (lot?) more difficult.

I've seen formulas in Excel that extract just numeric data from text, but I don't think those formulas would be able to be transferred to Access.

Quote:

---

Originally Posted by Norie

OBP

I don't know if the rest of the stuff can be done without VBA.

I think returning the filename without the extension would be easy using text functions but the other part regarding the prefix/suffix is a little (lot?) more difficult.

I've seen formulas in Excel that extract just numeric data from text, but I don't think those formulas would be able to be transferred to Access.

---

The prefix and suffix situation is indeed a sticky wicket. The code OBP attached seems to be creating a mixed bag of results. First, if a file name is all alpha characters it is not returning it as the prefix. Also, some of the prefixes and suffixes are not populating as you would expect. The behaviour seems inconsistent as on some file names it seems to function properly where on other it doesn't work at all.

Oddly, it seemed to work better when I used me!files vs. me.files.

alec, that is one of the quirks of VBA code, when to use Me. and when to use Me!
If it works better then use it.
Is there any chance of posting or emailing me the actual database to try and work out a better method.
I don't think that I looked at the "all Alpha" side of the file name, but that can easily be accomodated in the VB code.
Shall I do that?