Msaccess Read Data From a Text File

While databases and spreadsheets are indeed very versatile,
sometimes you have a demand to piece of work with text files. Possibly y'all were sent a text
file containing information you need to apply, or perchance you demand to be able to produce
a text file to send to someone.

While it's possible to read the text file and dispense information technology
using VBA, depending on how the data'due south arranged in the file, it'southward also possible
to treat the text file every bit a information table, and employ 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 commodity, I'm going to talk about a couple of ways
that you tin exercise just that.

A couple of warnings

Before going any further, let me country up forepart 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 tabular array. Figure i shows the error
message yous'll go it you effort.

Figure 1: The error message when trying to update a text
file – Unfortunately, it's just not possible to update a linked text table.

This doesn't mean that you lot cannot write to a text file: information technology
merely ways that you cannot delete information from or insert it to a text file as you
can from a table, nor tin can you update information existing in the file.

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

If you need to get effectually that restriction, one option is to
programmatically rename the file, piece of work with it, and then name it dorsum to its
original name. In VBA, you can use the Name argument to rename files. The post-obit
code volition rename file.xyz (in folder C:\Folder) to File.txt:

          
          Name "C:\Folder\File.xyz" Every bit "C:\Folder\File.txt"
        

Sometimes, though, you may exist working with files where you
cannot rename the file, either considering you lot don't have sufficient permissions on
the file, or considering others are too using the file. In that example, you might be
able to copy the file to a location where y'all do have permissions and work with
the re-create. In VBA, you can use the FileCopy statement. The following code volition
make a copy of File.xyz from folder East:\Folder to folder C:\Information, renaming the
file to File.txt at the same time:

          
          FileCopy "E:\Folder\File.xyz", "C:\Information\File.txt"

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

Figure ii: Arrangement Registry entries associated with text
files when using MDB files- Registry changes should never exist taken lightly, as
they can crusade serious problems that may require reinstallation of the
operating system.

If you're working with an ACCDB file, you're looking for the DisabledExtensions
value nether the HKEY_LOCAL_MACHINE\Software\Microsoft\Role\12.0\Admission
Connectivity Engine\Engines\Text subkey in the registry, equally shown in Figure iii.

Figure 3: Organisation Registry entries associated with text
files when using ACCDB files – The same cautions virtually making Registry changes
apply.

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

Figure 4: The Edit String window – Add the boosted
extension(s) to exist immune to the list.

Information technology may seem counterintuitive to add an extension to the DisabledExtensions
value when you want to enable use of that extension. However, note that the
get-go character in the value is an assertion indicate (!). Placing an
exclamation marking at the kickoff of the Value information allows yous to modify those
files with the extensions listed. When y'all do not place an assertion mark at
the outset of the Value data, yous cannot change 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 take only a single
period in the file proper name. While I know that it's possible to use filenames similar This
is the text file I created. Joe created a unlike file, but nosotros won't employ
it.txt, the lawmaking I'm presenting won't work properly with names like that.
While information technology'southward not difficult to rewrite the code and then that it will work with file
names like that, I didn't want to introduce that complication.

Simplistic Approach

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

Figure 5: The Import Text Wizard – The magician should
appear automatically when you go to import or link a text file, and walks you
through the procedure.

If you click on the Avant-garde push button (bottom left paw corner
of wizard), you get the pick to refine the definition, and even to salvage the
specification in the database (see Figure half dozen).

Figure 6: The Data Link Specification dialog – This
dialog allows you to specify a number of characteristics of the data, and to
salvage the specification should you wish to reuse it.

If you salve the specification, it'south actually stored in two
system tables (which are normally hidden), MSysIMEXSpecs and MSysIMEXColumns.
Once the specification has been stored in that location, you lot can then apply it
programmatically by passing the name of the specification every bit a parameter to the
TransferText method:

          
          DoCmd.TransferText acExportDelim, _
          "Standard Output", _
          "qryAprilDetails", "C:\Txtfiles\April.txt"
        

In that case, the details stored as specification
"Standard Output" would be used when exporting the information produced by qryAprilDetails
to tabular array C:\Txtfiles\April.txt.

Note that while the TransferText method about often is used to
import text files, the ability to create linked tables exists every bit well:
depending on the characteristics of your text file, y'all'd use either acLinkDelim
or acLinkFixed instead of acExportDelim in the instance in a higher place.

While it's not possible to programmatically create or
manipulate stored specifications, it is possible to import existing
specifications from one database into some other. When you go to through File |
Get External Data | Import and select the database, click on the Options button
in the bottom right paw corner of the Import Objects dialog (encounter Figure vii).
1 of the options available to you is to import the Import/Consign Specs, as
shown in Figure viii.

Effigy 7: The Import Objects dialog – Notation that the
Options button does non appear when linking.

Figure 8: The Import Objects options – This dialog gives
you some boosted capabilities when importing.

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 Admission, there
are times you might desire to go beyond those capabilities. Besides, you might
want to be able to utilise 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(function.eleven).aspx
"ISAM (Indexed Sequential Access Method) describes a kind of file type
where records are read and written in sequential order just can as well exist
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
every bit-needed basis. Jet comes with IISAMs for dBase, Excel, and text files. The
amount of functionality supported for each file type volition vary from IISAM to
IISAM."

When using the text IISAM, the format of the text file is
determined past using a schema information file. This file is always named
Schema.ini, and must exist in the aforementioned directory as the text file. Substantially,
each entry in the Schema.ini file specifies:

• The name of the text file
• The format of the text file (options are delimited, using whatsoever
  delimiter wanted other than double quotation marks or stock-still width)
• The names, widths and types of all of the fields in the text file
• The grapheme prepare used (ANSI or OEM. If non 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 three
          Col2=LastName Char Width 20
          Col3=FirstName Char Width 10
          Col4=Championship Char Width thirty
          Col5=TitleOfCourtesy Char Width 25
          Col6=BirthDate Engagement x
          Col7=HireDate Date 10
          Col8=Address Char Width 60
          Col9=City Char Width fifteen
          Col10=Region Char Width 15
          Col11=PostalCode Char Width x
          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 in a higher place indicates that the file Employees.txt
has column headers, that at that place are seventeen columns in the file, that the text
uses the ANSI graphic symbol fix and that the columns are delimited with tabs. (It
may exist worth pointing out that the fifteenth cavalcade, named Photo, is really
only a place holder. The IISAM just works with text, and so you cannot use it with
binary data such as images)

If yous wanted fixed width columns, without column headers,
and y'all wanted to ensure that the dates were in yyyy-mm-dd format (regardless
of what the machine'south Short Date format was set up to through Regional Settings),
yous 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 xxx
          Col5=TitleOfCourtesy Char Width 25
          Col6=BirthDate Engagement Width 10
          Col7=HireDate Date Width 10
          Col8=Accost Char Width 60
          Col9=Urban center 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 iv
          Col15=Photograph OLE Width ten
          Col16=Notes LongChar Width 450
          Col17=ReportsTo Integer Width four
          DateTimeFormat=yyyy-mm-dd
        

Note that because the specification in a higher place is for FixedLength,
information technology's necessary to provide a width for each column (it's optional for delimited
files).

It's non my intention to go through all of the details of the
Schema.ini file. If you lot desire more than data, cheque http://msdn.microsoft.com/en-the states/library/ms709353(VS.85).aspx

Doug Steele

Doug Steele has worked with databases, both mainframe and PC, for many years. Microsoft has recognized him as an Access MVP for his contributions to the Microsoft-sponsored newsgroups.

zieglercronts.blogspot.com

Source: https://www.databasejournal.com/ms-access/working-with-external-text-files-in-ms-access/

Share this post