ACC2000: Text Import Wizard Does Not Import Data Correctly (210057)

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.

1. Start Microsoft Access and open any database.
2. Create a new module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit

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

4. To run this function, type the following line in the Immediate window, and then press ENTER

   ? TestImportText("pathname\filename")

   where pathname\filename 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.