Working with Text Files

You can use the Text installable ISAM to link delimited and fixed-width text files and to open them directly from your Microsoft Access database. The procedure for doing this is very similar to the procedure for connecting to external databases. When you link a text file, you specify the source database type and the path to the text file in the Connect property, and the file name in the SourceTableName property. When you open a text file directly, you specify the source database type and the path to the text file as arguments of the OpenDatabase method, and the file name as an argument of the OpenRecordset method. When specifying a connection string for text files, use the specifications described in the following table.

For Use
Source database type
Text
Path The full path to the folder that contains the text file you want to access.
Source table name The name of the text file including the extension. If you don’t specify an extension, the default .txt extension is used.

In fixed-width text files, Microsoft Access recognizes Null values by the absence of data (spaces) in the field. In delimited text files, Microsoft Access recognizes Null values by the presence of two consecutive delimiting characters.

Note   Commas, tabs, or user-defined delimiters are valid in the source file.

The following table lists the size limitations of text files when linking or opening them.

Item Maximum size per text file
Field 255 fields
Field name 64 characters
Field width 32,766 characters
Record size 65,000 bytes

Important Microsoft Access doesn’t support multiuser access to text files. When you open a text file by using Microsoft Access, you have exclusive access to the file.

Creating a Schema Information File

Microsoft Access determines the format of a text file either by reading the file directly or by using a schema information file that overrides the default settings in the Windows Registry. The schema information file is always named Schema.ini. In order for Microsoft Access to use the information in the Schema.ini file, you must store it in the same folder as the text data source. A Schema.ini file is always required for accessing fixed-width data, and is recommended when your text file contains Date/Time, Currency, or numeric floating-point data, or any time you want more control over handling the data in the text file. You do not need a separate Schema.ini file for each text file you want to link.

You can use any text editor to create a Schema.ini file. In your Schema.ini file, you add entries, each of which specifies one of five characteristics of the text file:

  • The text file name
  • The file format
  • The field names, widths, and data types
  • The character set
  • Special data type formats and conversions
Specifying the File Name

The first entry in a Schema.ini file is always the name of the text source file enclosed in brackets. For example, to specify the file Sample.txt as your text data source, you would add the following text as the first entry in your Schema.ini file:

[Sample.txt]
Specifying the File Format

The Format entry in a Schema.ini file specifies the format of the text file, such as whether it is delimited or fixed-width. You can set the Format entry to any of the values listed in the following table.

Format value File format
TabDelimited Fields in the text file are delimited by tabs.
CSVDelimited Fields in the text file are delimited by commas.
Delimited(*) Fields in the text file are delimited by asterisks. You can substitute any character for the asterisk except the double (") quotation mark.
FixedLength Fields in the text file are of a fixed width.

For example, to specify a comma-delimited format, you would add the following entry to the Schema.ini file:

Format=CSVDelimited
Specifying the Field Names, Widths, and Data Types

For delimited text files, you can specify field names in one of two ways:

  • Include the field names in the first record of the text file and set the ColNameHeader entry to True to indicate that the first record of data specifies field names.
  • Specify each field by number and designate the field name and data type.

You can have Microsoft Access determine the data types of the fields for you. Set the MaxScanRows entry to the number of records Microsoft Access should scan when it determines the field data types. If you set the MaxScanRows entry to 0, Microsoft Access scans the entire file. For example, to specify that Microsoft Access should use the data in the first record in the text file as field names and should examine the entire file to determine the data type of each field, add the following entries to your Schema.ini file:

ColNameHeader=True
MaxScanRows=0

For fixed-width files, you must specify each field by number and designate the field name, data type, and width. To do so, set the Coln entry; the syntax of the Coln entry is:

Coln=ColumnName type [Width #]

The following table describes each part of the Coln entry.

Part Description
ColumnName The name of the field. If the field name contains embedded spaces, you must enclose it in double quotation marks.
type Specifies the data type for the field. It can be any of the following data types.
Microsoft Jet SQL data types:ByteLongCurrencySingleDoubleDateTimeTextMemo ODBC data types:Char (same as Text)Float (same as Double)Integer (same as Short)LongChar (same as Memo)Date date format
Width The literal string value Width. Indicates that the number that follows specifies the width of the field (required for fixed-width files).
# The integer value that specifies the number of characters in the field (required if Width is specified).

For example, to specify two fields, a 10-character CustomerID text field and a 30-character CustomerName text field, add the following entries to your Schema.ini file:

Col1=CustomerID Text Width 10
Col2=CustomerName Text Width 30
Specifying a Character Set

The CharacterSet entry specifies which character set your computer uses. You can specify one of two character sets: ANSI or OEM. For example, to specify the ANSI character set, add the following entry to your Schema.ini file:

CharacterSet=ANSI
Specifying Data Type Formats and Conversions

The Schema.ini file contains a number of entries that you can use to specify how data is converted or displayed when Microsoft Access reads it. The following table describes each of these entries.

Entry Description
DateTimeFormat A format string that specifies the format for dates and times. You should specify this entry if all Date/Time fields have the same format. You can use any Date/Time format except A.M. and P.M. display formats.
DecimalSymbol Specifies the character used to separate the integer from the fractional portion of a number.
NumberDigits Specifies the number of decimal digits in the fractional portion of a number.
NumberLeadingZeros Specifies whether a decimal value less than 1 and greater than –1 should contain leading zeros; you can set this entry to False (no leading zeros) or True.
CurrencySymbol Specifies the currency symbol used for currency values in the text file; for example, the dollar sign ($) or Dm.
CurrencyPosFormat Specifies the position of the currency symbol. You can set it to any of the following values:
0123 Currency symbol prefix with no separation ($1) Currency symbol suffix with no separation (1$)Currency symbol prefix with one character separation ($ 1)Currency symbol suffix with one character separation (1 $)
CurrencyDigits Specifies the number of digits used for the fractional portion of a currency value.
CurrencyNegFormat Specifies the format of negative currency values. You can set it to any of the following numbers:
0 ($1)

1 –$1

2 $–1

3 $1–

4 (1$)

5 –1$

6 1–$

7 1$–

8 –1 $

9 –$ 1

10 1 $–

11 $ 1–

12 $ –1

13 1– $

14 ($ 1)

15 (1 $)

The dollar sign ($) is shown only as an example; you should replace it with the appropriate currency symbol.
CurrencyThousandSymbol Specifies the character used to separate thousands in currency values.
CurrencyDecimalSymbol Specifies the character used to separate the whole from the fractional portion of a currency value.

Note   If you omit an entry, Microsoft Access uses the default value in Windows Control Panel.

Example of a Schema Information File

The following example of a Schema.ini file specifies the format for the fixed-width file Filename.txt and the delimited file Delimit.txt:

[Filename.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname Char Width 24
Col2=columnname2 Date Width 9
Col3=columnname7 Float Width 10
Col4=columnname8 Integer Width 10
Col5=columnname9 LongChar Width 10

[Delimit.txt]
ColNameHeader=True
Format=Delimited(!)
MaxScanRows=0
CharacterSet=OEM
Col1=username Text
Col2=dateofbirth DateTime

Linking Text Files

To link a text file to your Microsoft Access database, use the CurrentDb function to return an object variable that represents the current Microsoft Access database. Then create a TableDef object and set the connection string and source table name: set the TableDef object’s Connect property to specify the source database type and the full path to the text file, and set its SourceTableName property to the name of the text file.

Important If you want Microsoft Access to use the information in the schema information file, you must place the text file and the Schema.ini file in the same folder. For more information on schema information files, see “Creating a Schema Information File” earlier in this chapter.

The following example links a text file to a Microsoft Access database. It assumes that you want to link the Sample text file located in the Data subfolder of the My Documents folder.

Sub LinkTextFile()
	Dim dbs As Database
	Dim tdfSales As TableDef
	Dim rstSales As Recordset

	' Open the Microsoft Access database.
	Set dbs = CurrentDb

	' Create a TableDef object.
	Set tdfSales = dbs.CreateTableDef("Central Division Sales")

	' Set the connection string to specify the source database type and the
	' full path to the file that you want to link.
	tdfSales.Connect = "Text;DATABASE=C:\My Documents\Data"

	' Set the SourceTableName property to the name of the file you want to access.
	tdfSales.SourceTableName = "Sample.txt"

	' Append the TableDef object to the TableDefs collection to create the link.
	dbs.TableDefs.Append tdfSales

	' Create a Recordset object from the linked text file.
	Set rstSales = dbs.OpenRecordset("Central Division Sales")
End Sub

Tip Instead of specifying the connection string and source table name by setting properties of the TableDef object, you can specify them by using the connect and source arguments of the CreateTableDef method. You can also use the Microsoft Access user interface to link external data. For more information on using the CreateTableDef method or the user interface to link external data, see “Linking External Tables” earlier in this chapter.

Opening Text Files

To open a text file directly from your Microsoft Access database, create a Database object and set the arguments of the OpenDatabase method to specify the full path to the text file, whether to open the text file exclusively, whether to open it with read/write or read-only permissions, and the source database type. Specify the file name as the source argument of the OpenRecordset method.

The following example opens an external text file directly from a Microsoft Access database and then creates a Recordset object from the text file.

Sub OpenTextFile()
	Dim dbs As Database
	Dim rstAwards As Recordset

	' Point to a folder that contains the text file you want to open.
	Set dbs = OpenDatabase("C:\My Documents\Data", False, False, "Text;")

	' Create a Recordset object from the Awards text file.
	Set rstAwards = dbs.OpenRecordset("Awards.txt")
End Sub