ACC: Text Import Wizard Doesn't Import Data Correctly

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

Certain text files are created with only a Chr(10) (linefeed character) at the end of each line to indicate a new record. The Import Wizard expects both a Chr(13) and a Chr(10) to recognize the end of a record. Furthermore, the character codes must be in this order: Chr(13) + Chr(10). Because neither Microsoft Access 7.0 nor 97 see this combination, they interpret the imported data as one record. Microsoft Word and Microsoft Access 2.0 on the other hand, recognize Chr(10) to indicate the end of a record.

NOTE: Unix text files use the line feed character at the end of the line. OS/2 and MS-DOS text files use the line feed and the carriage return together at the end of the line.

This behavior is by design.

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 6.0 or later to search for paragraph marks and replace them with carriage returns/line feeds.

For more information about finding and replacing paragraph marks in Microsoft Word, search the Microsoft Word Help Index for "find and replace," and then "Examples of special characters and document elements you can find and replace."

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 will be able to successfully import the records.

This part of the article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

1. Open any database and create a new module. Type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

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

3. To run this function, type the following line in the Debug window, and then press ENTER
   ? TestImportText("<name of text file to import>")
   where <name of text file to import> 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 will appear asking for the name of the new text file that is going to be created. This will prevent the original text file from being overwritten. Type in the name of the new text file and click OK. This will create a new text file that is in the proper format for Microsoft Access 7.0 or 97 to import.

For more information about importing, search the Help Index for "importing data," or ask the Microsoft Access 97 Office Assistant.