Microsoft KB Archive/210057

= ACC2000: Text Import Wizard Does Not Import Data Correctly =

Article ID: 210057

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210057



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you use the Import Text Wizard to import certain text files, you may see one of the following symptoms:  The records in the imported file are combined into one continuous record. The Import Text Wizard stops responding (hangs), or your computer stops processing. You receive one of the following error messages:

External File isn't in the expected format.

-or-

Out of stack space.





CAUSE
Certain text files are created with only a Chr(10) (linefeed character) at the end of each line to indicate a new record. To recognize the end of a record, the Import Wizard expects both a Chr(13) and a Chr(10). Furthermore, the character codes must be in this order: Chr(13) + Chr(10). Because Microsoft Access 2000 does not "see" this combination, it interprets the imported data as one record.



RESOLUTION
Use one of the following three methods to work around this behavior.

Method 1
Use a text editor (such as Notepad) to insert a Chr(13) after each record.

Method 2
Use Microsoft Word to search for paragraph marks and replace them with carriage returns/line feeds.

For more information about finding and replacing paragraph marks inMicrosoft Word, click Microsoft Word Help on the Help menu, type find andreplace text or formatting in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Method 3
Use the following steps to create a Visual Basic for Applications function that checks to see if a file contains only a CHR(10) at the end of each record and, if so, replaces it with a CHR(13) + CHR(10) so that Microsoft Access is able to successfully import the records.  Start Microsoft Access and open any database. Create a new module, and then type the following line in the Declarations section if it is not already there:

Option Explicit

</li>  Type the following procedures: Function TestImportText(ImportTextFile As String) Dim x As String, y As String Dim NumberOfCarriageReturns As Long Dim NumberOfLineFeeds As Long

NumberOfCarriageReturns = 0 NumberOfLineFeeds = 0

Open ImportTextFile For Input As #1 Do Until EOF(1) Line Input #1, x   If InStr(1, x, Chr(13)) > 0 Then NumberOfCarriageReturns = NumberOfCarriageReturns + 1 End If   If InStr(1, x, Chr(10)) > 0 Then NumberOfLineFeeds = NumberOfLineFeeds + 1 End If Loop Close #1 'If no Carriage returns found, run the next function to modify 'the text file.

If NumberOfCarriageReturns < NumberOfLineFeeds And _ NumberOfLineFeeds > 0 Then Dim NameOfNewText As String NameOfNewText = InputBox( _     "Enter The Name Of The New TextFile" _      & " In Which To Save The Changes.") y = ImportText(ImportTextFile, NameOfNewText) End If End Function

Function ImportText(OldText As String, NewText As String) Dim x As String, Endvalue As Integer Dim StartValue As Integer, OutputTxt As String

Open OldText For Input As #1 Open NewText For Output As #2 Do Until EOF(1) Line Input #1, x    Endvalue = InStr(1, x, Chr(10)) StartValue = 1 Do Until Endvalue = 0 If Endvalue > 0 Then OutputTxt = Mid(x, 1, (Endvalue - 1)) Print #2, OutputTxt StartValue = Endvalue + 1 x = Mid(x, StartValue) Endvalue = InStr(1, x, Chr(10)) End If    Loop Loop Close #1 Close #2 MsgBox NewText & " Successfully created." End Function </li> To run this function, type the following line in the Immediate window, and then press ENTER

? TestImportText(" ")

where  is the location of your file; for example:

? TestImportText("C:\My Documents\testfile.txt")

If the text file does not need to show carriage returns, an input box appears, asking for the name of the new text file that is going to be created. This prevents the original text file from being overwritten. Type the name of the new text file and click OK. This creates a new text file that is in the proper format for Microsoft Access to import.</li></ol>

<div class="references_section">