PRB: Text Files That Use Non-Standard Text Delimiters Do Not Parse Properly with Microsoft Text Driver

Article translations Article translations
Article ID: 205439 - View products that this article applies to.
This article was previously published under Q205439
Expand all | Collapse all

SYMPTOMS

When you use data access technologies such as Microsoft Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADO) to open text files that contain a non-standard delimiter (such as a single quotation mark), the delimiter appears in the field data. Furthermore, text fields that contain an embedded field separator (such as a comma) do not parse properly.

For example, if a text file is formatted as follows:
'Doe, Jane','1 Main St.','Redmond','WA'
					
The recordset field data for the first column contains the following text:
'Doe
					
and not 'Doe, Jane' as expected. This problem does not occur if you use double quotation marks to delimit text fields.

CAUSE

This problem occurs because the Microsoft Jet engine text Indexed Sequential Access Method (ISAM) or Microsoft Text ODBC Driver, which is contained in Odbcjt32.dll or the microsoft.jet.oledb.x.x OLE DB Provider, expects either a double quotation mark or no text delimiter at all. If you use double quotation marks as the text delimiter in the above-mentioned text file, this parsing problem does not occur. This problem only occurs when you use non-standard text delimiters.

RESOLUTION

To resolve this problem, use one of the following methods:
  • If you use a non-standard text delimiter, you must define the delimiter in the Schema.ini file. For example, in Schema.ini, add the following line:
    TextDelimiter='
    						
  • In Notepad, create a new file in the same directory as the text file, and paste the following text:
    [MyTextFile.txt]
    ColNameHeader=False
    Format=CSVDelimited
    TextDelimiter='
    							
    NOTE: Make sure that you replace the single quotation mark in the following line:
    TextDelimiter='
    							
    with the delimiter that is used in the text file. Also, insert your particular file name between the square brackets.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
210073 ACC2000: How to Use Schema.ini to Access Text Data
210001 ACC2000: How to Programmatically Create a Schema.ini File

Properties

Article ID: 205439 - Last Review: December 3, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft Data Access Components 1.5
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.1 Service Pack 1
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 Service Pack 1
  • Microsoft Data Access Components 2.5 Service Pack 2
  • Microsoft Data Access Components 2.6
Keywords: 
kbjet kbprb KB205439

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com