You may receive an error message when you try to import data or to link to data in a large text file that contains null values in Access 2003 or Access 2002 (872914)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002

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

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

SYMPTOMS

In Microsoft Access, when you try to import the data or to link to the data in a fixed-width text file, Microsoft Access stops responding for a long duration, and you receive the following error message:
One or more rows of data in your file contain too many characters to import. The maximum characters per row is 65000.
After you click OK in the dialog box that contains the error message, the Import Text Wizard or the Link Text Wizard starts. However, the data in the text file does not appear correctly in the Import Text Wizard or the Link Text Wizard. Therefore, you cannot successfully import the data or link to the data in the text file.

CAUSE

This problem occurs when the following conditions are true:
  • The fixed-width text file is very large.
  • The fields in the fixed-width text file contain null values that are embedded.

WORKAROUND

To work around this problem, you must replace the null values in the fixed-width text file with a blank space. Then, you can import the data or link to the data in the fixed-width text file. To do this, follow these steps:
  1. Start Microsoft Access.
  2. Open the Access database or the Access project that you want to import the data to or that you want to link from.
  3. In the Database window, click Modules in the Objects section, and then click New.
  4. Paste the following code in the Visual Basic Editor. This code replaces the null values with blank spaces and then copies the contents of the fixed-width text file to a new text file.
    Sub WriteNewTextFile()

    Dim characterArray() As Byte
    Dim fileLen As Long
    Dim strOrigFile As String
    Dim strNewFile As String
    Dim MyString As String
    Dim fs As Object
    'Change the path and the names of the files according to your requirement.
    strOrigFile = "<Full path of your original text file>"
    strNewFile = "<Full path of the new text file>"

    Set fs = CreateObject("Scripting.FileSystemObject")
    If (fs.FileExists(strOrigFile)) Then
    
        'Open the file and obtain the length
        Open strOrigFile For Binary As #1
        fileLen = LOF(1)
    
        'Read the file
        ReDim characterArray(fileLen) As Byte
        Get #1, , characterArray
        Close #1
    
        'The problem with the file occurs because the file contains null values that are embedded
        Dim i As Long
    
        For i = 1 To fileLen
        'If the character is a null value, change it to a blank space like Notepad does
            If (characterArray(i) = &H0) Then
                characterArray(i) = &H20
            End If
        Next i
    
    'Write the replacement file
        Open strNewFile For Binary As #1
        Put #1, , characterArray
        Close #1

        MsgBox "Completed"
    Else
        MsgBox "Provide valid path of the text file"
    End If
End Sub
    Note In the code, you must replace <Full path of your original text file> with the correct path of your fixed-width text file. You must also replace <Full path of the new text file> with the correct path of the new text file.
  5. In the Visual Basic Editor, click Immediate Window on the View menu.
  6. In the Immediate window, type WriteNewTextFile, and then press ENTER.
A new text file is created. You can now import the data or link to the data that is contained in the new fixed-width text file.

MORE INFORMATION

In a Microsoft Access 97 database, if you try to import data or to link to data in the same fixed-width text file that contains null values that are embedded, the data is imported or linked successfully.

In a Microsoft Access 2000 database, if you try to import data or to link to data in the same fixed-width text file that contains null values that are embedded, the Import Text Wizard or the Link Text Wizard appears immediately. However, the data in the text file does not appear correctly, and you may notice unrecognized characters in the Import Text Wizard or the Link Text Wizard. You cannot successfully import the data or link to the data in the text file. Access 2000 does not stop responding, and you do not receive the error message that is mentioned in the "Symptoms" section.

REFERENCES

For more information about how to import data or to link to data in Microsoft Access 2002, click Microsoft Access Help on the Help menu, type Import or link data and objects in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about how to import data or to link to data in Microsoft Office Access 2003, click Microsoft Office Access Help on the Help menu, type Import or link data and objects in the Search for box in the Assistance pane, and then click Start searching to view the topic.
Modification Type:MinorLast Reviewed:7/28/2006
Keywords:kbnewfile kbfile kbCodeSnippet kbImport kbprb kbtshoot kberrmsg KB872914 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.