Microsoft KB Archive/254002

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

Article ID: 254002

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q254002



SYMPTOMS
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.



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



RESOLUTION
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.



STATUS
This bug has been fixed in Microsoft Jet 4.0 SP4.



MORE INFORMATION
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
  Using Microsoft Notepad, create a text file with the following data, and save it as: C:\TEST.TXT: LastName,Gender Smith,F Jones,F  In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project. On the Project menu, select References, and make a reference to the following type library: Microsoft DAO 3.6 Object Library

  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  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

</li></ol>

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.

<div class="references_section">