|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 872914 - Last Review: December 6, 2006 - Revision: 3.2 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
This article applies only to a Microsoft Access database (.mdb).
Moderate: Requires basic macro, coding, and interoperability skills.
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. 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.
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 = "<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
- In the Visual Basic Editor, click Immediate Window on the View menu.
- 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. 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.
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.
APPLIES TO- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
| kbnewfile kbfile kbcodesnippet kbimport kbprb kbtshoot kberrmsg KB872914 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
Lisa Chow
- lisachow02 NOSPAM-AT-NOSPAM hotmail.com
|
Report As Irrelevant
|
| Written:
7/12/2005 11:00 AM |
|
|
|
|
 |
|
 |
|
|
|
| |