FIX: Importing Text File Without Schema.ini Can Cause Text Column to Be Defined as Currency

When importing a text file into a Microsoft Jet database through a Microsoft SQL statement or the Microsoft Access TransferText macro command when there is no Schema.ini file present, the Jet engine can sometimes interpret a column with Text data as Currency.

The Microsoft Jet database engine examines the column for currency formatting symbols.

To resolve this problem, use one of the following methods:

• Upgrade to Microsoft Jet 4.0 SP4.
• Use the Access 2000 Import Wizard.
• Create a Schema.ini file, which can contain a list of data types for each column in the text file.

This bug has been fixed in Microsoft Jet 4.0 SP4.

When determining whether the data type of a column in a text file is Currency, Microsoft Jet checks to see if there are characters that are not allowed in the Currency format. If there are none, it assumes that the data type is Currency. However, a column with a single character, such as the letter "F," can be considered a Currency column because this character is allowed in the Currency format. The fix causes a more extensive check to be made.

Steps to Reproduce the Problem

1. Using Microsoft Notepad, create a text file with the following data, and save it as: C:\TEST.TXT:

   LastName,Gender
   Smith,F
   Jones,F
   					

2. In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project.
3. On the Project menu, select References, and make a reference to the following type library: Microsoft DAO 3.6 Object Library
   
   
4. Add a Command button and the following code to the default form:

   Option Explicit
   
   Private Sub Command1_Click()
   Dim db As dao.Database, td As dao.TableDef, F As dao.Field
     Set db = DBEngine(0).OpenDatabase("nwind.mdb")
     db.Execute "SELECT * INTO Table1_CSV FROM [text;hdr=yes;database=c:\].[test#txt]", dbFailOnError
     Set td = db!Table1_CSV
     Set F = td.Fields(1)
     Debug.Print F.Name & ": " & F.Type, "Text: " & dao.dbText, "Currency: " & dao.dbCurrency
     Set F = Nothing
     Set td = Nothing
     db.Execute "DROP TABLE Table1_CSV", dbFailOnError
     Set db = Nothing
   End Sub
   					

5. Run the code and click the Command button. In the Immediate window, you see the following output if you are using Microsoft Jet 4.0 SP3 or earlier showing that the Gender field was treated as if it contained Currency data:
   Gender: 5 Text: 10 Currency: 5
   If you have Microsoft Jet 4.0 SP4 or later, the output appears as follows, showing it was correctly interpreted as a text column:
   Gender: 10 Text: 10 Currency: 5

NOTE: This problem manifests using any Jet access method, including the Microsoft Access ODBC driver, the Microsoft Jet OLDB provider, and the Microsoft Access TransferText macro command. The Access 2000 Import Wizard does not exhibit the problem because it makes a Schema.ini file and sets the data type to Text.

The following sources provide more information on the SCHEMA.INI file: