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

Article translations Article translations
Article ID: 872914 - View products that this article applies to.

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

Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

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

Properties

Article ID: 872914 - Last Review: December 6, 2006 - Revision: 3.2
APPLIES TO
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbnewfile kbfile kbcodesnippet kbimport kbprb kbtshoot kberrmsg KB872914

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com