Importing error in Access 2003

[Rick Charnes]

I'm suddenly getting this error that others have gotten in Access 2003
when importing a text file:

"One or more rows of data in your file contain too many characters to
import. The maximum characters per row is 65000."

I've seen the Microsoft KB article about this which suggests that it
happens when your text file "contains null values that are embedded".
What does this mean? It's a plain old text file, tab-delimited, created
by BCP, nothing special about it. Thanks.

 

[John Nurick]

Hi Rick,

One possibility is that the message means exactly what it says. If you
have Perl on your machine, this command gets you the numbers of any
over-length lines:

perl -ne"print qq($.\n) if length($_) > 65000" myfile.txt

Another is, as the KB article suggests, that there are null values in
the data. These are ASCII (or Unicode) NULLS, Chr(0), and aren't the
same as NULL in a data field. In a text file it's normally safe to
replace nulls with spaces (Chr(0) with " "); the exception is if the
nulls are in binary fields embedded in the text file.

This
perl -ne"print qq($.:\t$_) if m/\000/" myfile.txt
shows the line numbers and contents of any lines that contain nulls, and
this
perl -i.bak -pe"s/\000/ /g" myfile.txt
replaces nulls with spaces, leaving the original file renamed .bak.

Another possibility is quotes in the data. If your import specification
includes " as a text qualifier and a field begins with a " but does not
end with one, the import routine assumes the field value runs on across
field separators and line breaks until it reaches another ". Normally,
tab-delimited files don't use text qualifiers and the setting in the
import specification should be None. This

perl -ne"$q = s/\042/\042/g; print qq($.: $q\n) if $quotes % 2"
myfile.txt

lists the numbers of lines that have odd numbers of " characters and
therefore may be the source of this problem.

 

[Rick Charnes]

Thanks much. The lines aren't oversized so the error message isn't
quite accurate.

I don't have Perl on my machine so I'll have to figure out another way
to hunt for ASCII nulls.

The thing that I'm puzzled about is others have suggested that the
problem relates to the end-of-line character rather than the presence of
nulls. If I load the file in Notepad and then save it as UNICODE,
Access 2003 can then read it. I'd assumed that this ensures that the
EOL char is CR/LF rather than just LF, but maybe it eliminates ASCII
nulls as well? (Actually, I'm not sure what it does!)

Thanks for your help.

 

[John Nurick]

Yes, it could be the line breaks - especially if the file comes from a
non-Windows system. A web search will find a number of free utilities
that can convert line breaks to the Windows-style CRLF.