Working with external text files in MS Access

While databases and spreadsheets are indeed very versatile,
sometimes you have a need to work with text files. Perhaps you were sent a text
file containing data you need to use, or perhaps you need to be able to produce
a text file to send to someone.

While it’s possible to read the text file and manipulate it
using VBA, depending on how the data’s arranged in the file, it’s also possible
to treat the text file as a data table, and use the standard approaches to dealing
with tables instead. This has a major advantage in that you can use SQL to
filter the text in the file.

In this article, I’m going to talk about a couple of ways
that you can do just that.

A couple of warnings

Before going any further, let me state up front that while it’s
possible to read from a text file as though it was a table, it’s not possible
to update a text file as though it was a table. Figure 1 shows the error
message you’ll get it you try.

This doesn’t mean that you cannot write to a text file: it
just means that you cannot delete data from or insert it to a text file as you
can from a table, nor can you update data existing in the file.

Another potential issue is that, by default, you’re limited
(at least in Access) to what file extensions you can use. Out of the box, the
only file extensions you can use are txt, csv, tab and asc.

If you need to get around that restriction, one option is to
programmatically rename the file, work with it, then name it back to its
original name. In VBA, you can use the Name statement to rename files. The following
code will rename file.xyz (in folder C:\Folder) to File.txt:

Name “C:\Folder\File.xyz” As “C:\Folder\File.txt”

Sometimes, though, you may be working with files where you
cannot rename the file, either because you don’t have sufficient permissions on
the file, or because others are also using the file. In that case, you might be
able to copy the file to a location where you do have permissions and work with
the copy. In VBA, you can use the FileCopy statement. The following code will
make a copy of File.xyz from folder E:\Folder to folder C:\Data, renaming the
file to File.txt at the same time:

FileCopy “E:\Folder\File.xyz”, “C:\Data\File.txt”

If that’s not possible, the other option is to modify the
System Registry. If you’re working with an MDB file in either the Access 2000
file format or the Access 2002/2003 file format, you’re looking for the DisabledExtensions
value under the HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text subkey
in the registry, as shown in Figure 2.

If you’re working with an ACCDB file, you’re looking for the DisabledExtensions
value under the HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access
Connectivity Engine\Engines\Text subkey in the registry, as shown in Figure 3.

Double-click on the DisabledExtensions label, and the Edit
String window will open up, allowing you to add additional extensions to the
list of extensions allowed, as shown in Figure 4.

It may seem counterintuitive to add an extension to the DisabledExtensions
value when you want to enable use of that extension. However, note that the
first character in the value is an exclamation point (!). Placing an
exclamation mark at the beginning of the Value data allows you to modify those
files with the extensions listed. When you do not place an exclamation mark at
the beginning of the Value data, you cannot modify files with the extension
listed.

Finally, I’m making a simplifying assumption that all files
are what I’ll call "proper" file names, i.e.: they have only a single
period in the file name. While I know that it’s possible to use filenames like This
is the text file I created. Joe created a different file, but we won’t use
it.txt, the code I’m presenting won’t work properly with names like that.
While it’s not difficult to rewrite the code so that it will work with file
names like that, I didn’t want to introduce that complexity.

Simplistic Approach

The easiest approach in Access, of course, is to manually
link to or import your text file. When you go through File | Get External Data
and select a text file, the Import Text Wizard (see Figure 5) walks you through
the process of defining what the table should look like.

If you click on the Advanced button (bottom left hand corner
of wizard), you get the option to refine the definition, and even to save the
specification in the database (see Figure 6).

If you save the specification, it’s actually stored in two
system tables (which are normally hidden), MSysIMEXSpecs and MSysIMEXColumns.
Once the specification has been stored there, you can then use it
programmatically by passing the name of the specification as a parameter to the
TransferText method:

DoCmd.TransferText acExportDelim, _

  “Standard Output”, _

  “qryAprilDetails”, “C:\Txtfiles\April.txt”

In that example, the details stored as specification
"Standard Output" would be used when exporting the data produced by qryAprilDetails
to table C:\Txtfiles\April.txt.

Note that while the TransferText method most often is used to
import text files, the ability to create linked tables exists as well:
depending on the characteristics of your text file, you’d use either acLinkDelim
or acLinkFixed instead of acExportDelim in the example above.

While it’s not possible to programmatically create or
manipulate stored specifications, it is possible to import existing
specifications from one database into another. When you go to through File |
Get External Data | Import and select the database, click on the Options button
in the bottom right hand corner of the Import Objects dialog (see Figure 7).
One of the options available to you is to import the Import/Export Specs, as
shown in Figure 8.

Using this technique, you can create linked tables whenever
you need to, and you can export data to text files.

Using IISAM

While the technique outlined above works with Access, there
are times you might want to go beyond those capabilities. As well, you might
want to be able to use the same approach from other Office applications such as
Word or Excel. This is where you might use IISAM (Installable ISAM).

As Michael Kaplan explained in http://msdn.microsoft.com/en-us/library/aa160682(office.11).aspx
"ISAM (Indexed Sequential Access Method) describes a kind of file type
where records are read and written in sequential order but can also be
retrieved using indexes and keys. Most desktop database systems (Jet, dBase,
Paradox) are, in some sense of the term, ISAM systems. IISAM (Installable ISAM)
allows you to load the code to read and write a specific database on an
as-needed basis. Jet comes with IISAMs for dBase, Excel, and text files. The
amount of functionality supported for each file type will vary from IISAM to
IISAM."

When using the text IISAM, the format of the text file is
determined by using a schema information file. This file is always named
Schema.ini, and must be in the same directory as the text file. Essentially,
each entry in the Schema.ini file specifies:

• The name of the text file
• The format of the text file (options are delimited, using any
  delimiter wanted other than double quotation marks or fixed width)
• The names, widths and types of all of the fields in the text file
• The character set used (ANSI or OEM. If not provided, the
  information is determined from the Windows Registry)
• Any special data conversions (date/time format, numeric format,
  currency format)

Examples are:

[Employees.txt]

ColNameHeader=True

CharacterSet=ANSI

Format=TabDelimited

Col1=EmployeeID Integer 3

Col2=LastName Char Width 20

Col3=FirstName Char Width 10

Col4=Title Char Width 30

Col5=TitleOfCourtesy Char Width 25

Col6=BirthDate Date 10

Col7=HireDate Date 10

Col8=Address Char Width 60

Col9=City Char Width 15

Col10=Region Char Width 15

Col11=PostalCode Char Width 10

Col12=Country Char Width 15

Col13=HomePhone Char Width 24

Col14=Extension Char Width 4

Col15=Photo OLE

Col16=Notes LongChar

Col17=ReportsTo Integer 3

The specification above indicates that the file Employees.txt
has column headers, that there are seventeen columns in the file, that the text
uses the ANSI character set and that the columns are delimited with tabs. (It
may be worth pointing out that the fifteenth column, named Photo, is really
only a place holder. The IISAM only works with text, so you cannot use it with
binary data such as images)

If you wanted fixed width columns, without column headers,
and you wanted to ensure that the dates were in yyyy-mm-dd format (regardless
of what the machine’s Short Date format was set to through Regional Settings),
you could use:

[EmployeesExample1.txt]

ColNameHeader=False

CharacterSet=ANSI

Format=FixedLength

Col1=EmployeeID Integer Width 4

Col2=LastName Char Width 20

Col3=FirstName Char Width 10

Col4=Title Char Width 30

Col5=TitleOfCourtesy Char Width 25

Col6=BirthDate Date Width 10

Col7=HireDate Date Width 10

Col8=Address Char Width 60

Col9=City Char Width 15

Col10=Region Char Width 15

Col11=PostalCode Char Width 10

Col12=Country Char Width 15

Col13=HomePhone Char Width 24

Col14=Extension Char Width 4

Col15=Photo OLE Width 10

Col16=Notes LongChar Width 450

Col17=ReportsTo Integer Width 4

DateTimeFormat=yyyy-mm-dd

Note that because the specification above is for FixedLength,
it’s necessary to provide a width for each column (it’s optional for delimited
files).

It’s not my intention to go through all of the details of the
Schema.ini file. If you want more information, check http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx