Microsoft KB Archive/182354

= PRB: Jet Text IISAM Driver Drops Leading Spaces =

Article ID: 182354

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic for Applications 5.0
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q182354



SYMPTOMS
When importing a delimited text file into a Jet database, leading spaces are truncated from field data.



CAUSE
The TEXT IISAM driver for Jet truncates spaces from fields that are not delimited by the text qualifier character.



RESOLUTION
Make sure you use quotes or other text qualifier around your fields if you need to preserve leading spaces in your data.



STATUS
This behavior is by design.



MORE INFORMATION
WARNING: Your use or modification of the CODE provided in this article is at your own risk. Microsoft provides this CODE "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications of the CODE to suit specific customer requirements.

Steps to Reproduce Behavior
  Using Notepad, create a text file with the following data: ID,Desc Line1, Description 1 Line2," Description 2"  Save the file as c:\test.txt. In Visual Basic, create a new project and add a reference for Microsoft DAO 3.5 Object Library. In Access, open the Northwind.MDB database.  Create a new form with a CommandButton (Command1) and add the following code: Private Sub Command1_Click Dim db As Database Set db = CurrentDB                              ' Access only Set db = DBEngine(0).OpenDatabase("biblio.mdb") ' Visual Basic db.Execute "SELECT * INTO TestImport FROM [test#txt] IN '' " _ "'text;database=c:\;FMT=Delimited;HDR=Yes'" db.Close End Sub  Run the project and click the CommandButton to import the data.</li> Examine the contents of the TestImport table and note that the Desc column has the leading spaces truncated for Description 1, which did not use quotes, but they are present for Description 2, which did use quotes.</li></ol>

Notes:


 * 1) The symptoms are also present when using Access' File|Get External Data menu command and via the TransferText macro action and the TransferText method of the DoCmd object, all of which use the Text IISAM driver.
 * 2) The symptoms probably also apply to previous versions of Jet but have not been tested with them.

<div class="references_section">