Search The Web

Today's Headlines

Friday, April 15, 2011

Microsoft Access Tips & Tricks: Parsing File Names

In some database applications, one of the inputs required from the user may be a file name, including its path (basically the location of a document of some sort). In this post, I will discuss how such an input can be broken up into various component parts using basic string processing functions. Even if your application does not require the same operations to be performed on file names, you might get some insights into how string functions in Access work, and you can use that insight for other applications that require other forms of string processing.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), flattening a table (using VBA) , and cascading comboboxes.

I have talked about the use of the Windows File Picker in this earlier post. The code used in that post includes functions such as ExtractPathname(), but I did not provide code for some of these functions in that post. In this post, I will take a full file name (including the path and extension), and provide the code that does the following things:

  • Extract the file path
  • Extract the file name (without the path, but with the extension)
  • Extract the file name (no path, no extension)
  • Extract the file extension (gives you the file type)
  • Convert the file path into a relative path if the full path points to a location below the current location of the database file
  • Convert a relative path to the absolute file path assuming that the relative path refers to a location below the current location of the database file
Extracting the file path

The directory separation character in Windows (and pretty much any Microsoft operating system since the days of DOS) is the "\" character. In a full file name, everything up to and including the last "\" in the name is the file path, and what follows that is the actual file name (including the extension). Therefore, extracting the file path simply involves locating the last "\" in the full file name, and copying out everything upto and including that "\".

Public Function ExtractPathname(fullFileName As String) As String
'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns
'"C:\Document\DigitalBooks\Fiction\"

ExtractPathname = Left(fullFileName, InStrRev(fullFileName, "\"))

End Function
Extracting the file name (without the path, including the extension)

In a full file name, everything up to and including the last "\" in the name is the file path, and what follows that is the actual file name (including the extension). Therefore, extracting the file name simply involves locating the last "\" in the full file name, and copying out everything that follows that "\".

Public Function ExtractFilename(fullFileName As String) As String
'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "Filename.doc"

Dim lastBS As Integer

lastBS = InStrRev(fullFileName, "\")
ExtractFilename = Right(fullFileName, Len(fullFileName) - lastBS)

End Function
Extracting the file name (no path, no extension)

We will use the ExtractFileName() function we defined above to first get the file name including the extension. After that, we will remove the extension and return just the file name without the extension. Remember that the extension is simply what follows the last "." in the file name. Remember also, that this is true only on Windows systems. If you are working with file names from other operating systems, you might have to account for differences such as, but not limited to, filenames starting with a "." (such file names are very common on Unix-based operating systems).

Public Function ExtractFilenameNoExt(fullFileName As String) As String
'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "Filename"

Dim lastDot As Integer

fileName = ExtractFilename(fullFileName)
lastDot = InStrRev(fileName, ".")
If lastDot <> 0 Then
ExtractFilenameNoExt = Left(fileName, lastDot - 1)
Else
ExtractFileNameNoExt = fileName
End If

End Function
Couple of points to note about the function above: first, notice that we check whether the value of lastDot is 0. If it is, then there is no "." in the file name, and therefore, the file name does not contain an extention. In that case, the file name is actually the file name without the extension. Second, if we do find the ".", the file name without extension is everything up to but NOT including the ".". That is why we have to subtract 1 from lastDot in the call to Left().

Extracting the file extension


Remember, once again, that the extension is simply what follows the last "." in the file name. Remember also, that this is true only on Windows systems. If you are working with file names from other operating systems, you might have to account for differences such as, but not limited to, filenames starting with a "." (such file names are very common on Unix-based operating systems). Moreover, file names might have no extensions, though this is rare on Windows systems (not so rare in other operating systems such as Unix and its derivatives). If there is no "." in the filename with extension, then the file has no extension and the extension should be set to an empty string.

Public Function ExtractExtension(fullFileName As String) As String
'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "doc"

Dim lastDot As Integer

lastDot = InStrRev(fullFileName, ".")
If lastDot <> 0 Then
ExtractExtension = Right(fullFileName, Len(fullFileName) - lastDot)
Else
ExtractExtension = ""
End If

End Function
Converting the file path into a relative path

This is functionality that is not commonly needed, but can be very useful. I need it so that, inside my ebooks database, I can store the relative paths of the ebooks rather than the full paths. This allows me to take the root folder of my ebooks location (which is also where my ebooks database resides along with multiple folders for my actual ebooks), and move it to any other location on my hard disk, or even to an external disk. As long as the relative paths from the database to the actual ebooks does not change, the locations of the ebooks stored inside the database are valid, and I can get to any of them easily. Otherwise, every time I moved my ebooks collection, I would have to go in and change all the ebook locations in my database. It would be a nightmare.

To be more concrete, let us assume that my ebooks database resides at C:\Document\Digital Books. Under C:\Document\Digital Books, I also have other subfolders such as Fiction, Non Fiction, Comics, Series, Collections, etc., that actually contain my ebooks inside them. If I stored "C:\Document\DigitalBooks\Fiction\Filename.doc" inside my database as the location of a particular ebook, the database would lose the ability to locate that book if I moved my entire collection to another hard disk designated as D:. My database would now be at D:\Document\DigitalBooks, and filename.doc would now be at D:\Document\DigitalBooks\Fiction\Filename.doc. In fact, the database would not be able to locate that book if I decided to move my entire database inside my Documents and Settings folder, resulting in the new path to the ebook becoming "C:\Documents and Settings\Blogannath\My Documents\DigitalBooks\Fiction\Filename.doc".

The relative path from my database to the ebook never changes in any of the above moves. It is always ".\Fiction\Filename.doc". That is the motivation behind storing ebook locations with relative paths rather than absolute paths. In any case, in spite of this long explanation, the actual code for converting absolute paths to relative paths is quite simple as long as the absolute path starts with the database location.

Public Function GetRelativePath(fullFileName as String)

'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns ".\Fiction\Filename.doc"
'if the database resides at "C:\Document\DigitalBooks".

GetRelativePath = Replace(fullFileName, currentProject.Path, ".")

End Function

Converting the relative file path into the absolute path

This operation is necessary to issue commands to the operating system to open the given file when I want to read an ebook. A relative path with respect to the database location is not the relative path with respect to the application that opens the ebook in most cases (the file may need to be opened with Adobe Reader when the ebook is a PDF file, for instance, and Adobe Reader is not installed in the same location as the ebooks database). The conversion from relative path to absolute path is quite straightforward as shown below:

Public Function GetAbsolutePath(fullFileName as String)

'".\Filename.doc" returns "C:\Document\DigitalBooks\Fiction\Filename.doc"
'if the database resides at "C:\Document\DigitalBooks".

GetAbsolutePath = Replace(fullFileName, ".\", currentProject.Path & "\")

End Function

Notice that we use ".\" in the Replace() function call rather than simply ".". This is absolutely necessary because the Replace() function would otherwise replace all dots in the supplied argument with the path where the database is located. This would include the dots in the file name, including the one that separates the file name from its extension, among others! So, we have to search for ".\", and then replace that with the concatenation of currentProject.Path with "\". The currentProject.path does not contain a trailing "\", so we have to supply it ourselves.

Hope this post has been helpful in solving any problems you might have had with parsing file names in Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

3 comments:

Kuba Jewgieniew said...

Your blog posts is really great. A good source of information. Thanks for sharing anyway.

Kuba Jewgieniew

Anonymous said...

Great resource! Thanks!

I wanted to point out a couple of errors:

ExtractPathName = Left( line is missing ) parenthesis at the end.

ExtractExtension = Right( line has an extra parenthesis at the end.

Blogannath said...

Thank you for pointing out those errors. I have now corrected the post.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web