Microsoft KB Archive/872914

= 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 ID: 872914

Article Last Modified on 12/6/2006

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



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:  Start Microsoft Access. Open the Access database or the Access project that you want to import the data to or that you want to link from. In the Database window, click Modules in the Objects section, and then click New.  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 = &quot;&quot; strNewFile = &quot;&quot;

Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot;) 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 &quot;Completed&quot; Else MsgBox &quot;Provide valid path of the text file&quot; End If End Sub Note In the code, you must replace  with the correct path of your fixed-width text file. You must also replace  with the correct path of the new text file.  In the Visual Basic Editor, click Immediate Window on the View menu.</li> In the Immediate window, type WriteNewTextFile, and then press ENTER.</li></ol>

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.

<div class="moreinformation_section">

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 &quot;Symptoms&quot; section.

<div class="references_section">