http://www.bugnet.com/analysis/excel_export.html
KeyLabs Confirms...
Portable Excel 97 Export Bug Affects Word, WordPerfect, Paradox And Others
BUGNET HAS discovered a widespread, bonehead export bug in Microsoft Excel
97.
There is no mention of the problem in Microsoft's online Knowledge Base,
but tests conducted by KeyLabs have confirmed that the bug afflicts
various products from Microsoft and Corel.
"Here is a portable bug that occurs in Access 97, Word 97, Quattro 8,
WordPerfect 8 and Paradox 8," said David Sherman of Columbus, OH, who was
bitten by the bug.
"Take an Excel file. Create any fields that you want. Make sure that one
of them is a number(general) format with at least 12 characters. Save the
file as a *.CSV file.
"Now try to a merge (import) into Microsoft Word 97, Corel WordPerfect 8
and Lotus WordPro 97. WordPro is the only one that works."
Say what? You mean that Excel -- the most popular Windows spreadsheet --
can't export large numbers in the common .CSV format that can be read by
the two most popular Windows word processors, including Microsoft's own?
You got it!
TESTS PERFORMED by KeyLabs at BugNet's request both confirmed the bug and
shed some light on the cause.
When a 12-digit number like 123456789999 is exported by Excel in .CSV
format, it looks like 1.234567E+11.
"When you attempt to merge the Excel export file into WordPerfect,"
KeyLabs reports, "it considers 1.234567E+11 to be a text string because
there is a '+' character in the field..."
WordPerfect produces the error: "the file you specified is not a
spreadsheet, database or ASCII file."
Microsoft's own Word won't use the Excel .CSV export file either, stating
that "Word was unable to open data source."
Meanwhile, Corel Quattro Pro puts all records in a single spreadsheet
cell.
KeyLabs tests reveal that Corel Paradox also chokes on Excel 97 .CSV files
if they contain 12 digit or larger numbers.
MICROSOFT OFFICE Product Manager John Duncan responds that Excel is
designed so that the Number(general) format follows the conventions of
scientific notation. Problematic Excel values -- e.g., 123456789999
represented as 1.234567E+11 -- are valid expressions in terms of
scientific notation, Duncan notes.
"We do it this way because we think this is the way our customers want it
this way," says Duncan, "and we would take heat if we didn't."
Duncan adds that if users want to export very large numbers via .CSV and
have them represented as a literal number string, they should format the
cells with the Number format.
Good to know, especially for users like Sherman, who reasonably assume
that a number in Excel's Number(general) format should be the same number
when exported -- silly boy!
UNFORTUNATELY, this sad little saga is not a rarity, nor is Microsoft the
only culprit. Lotus and WordPerfect (under various corporate overlords)
have also had a significant amount of trouble with importing/exporting
data to other apps.
Recent examples include the poor vision of Lotus Notes 4's viewer for
Microsoft Excel files, and text color problems when Corel WordPerfect 8's
text converter is used to save files in WordPerfect 5.x format, and then
imported into Microsoft Word 97 or Publisher 97 (see BugNet Database,
6/26/98).
Similarly, an earlier version of the WordPerfect 6-7-8 import filter that
comes with Adobe PageMaker 6.5 misread several characters, such as
quotation marks, em dashes, etc. (see BugNet Database, 3/26/98).
And don't get me started on the subject of .RTF file conversion between
the products of various vendors, which was like a Russian novel.
IMPORTING AND exporting data between competitors' products has been such a
persistent problem that you might think it was some sort of widespread
conspiracy -- if it wasn't for the fact that all these vendors have
trouble importing and exporting between their OWN products.
An all-time example of this was the "Honey, I Shrunk the Fonts Bug" that
BugNet Senior Editor Bruce Kratofil discovered in Microsoft Internet
Assistant 2.0z for Word 95.
This nasty little hummer shrunk the font size of any text block with a
specific font tag assignment by two whole sizes, beginning with the second
line of the text.
And since Microsoft uses its own Internet tools in house, there were once
tens of thousands of pages on Microsoft's own web site which graphically
displayed this problem.
Microsoft's official response to BugNet on the "Honey, I Shrunk the Fonts
Bug" was that this was not a bug, but a feature. The "feature" was fixed
early this year, or may just have vanished with the disappearance of
Internet Assistant 2.0z produced documents.
Regarding the Excel .CSV export problem, Microsoft's position is the same.
"This is definitely not a bug," says John Duncan, "it's a feature."
BugNet hopes that this feature can be fixed too, perhaps by renaming the
Number(general) to something like Number(scientific), or providing a
warning message.
-- Bruce Brown
-o-
Subscribe: mail majordomo@repsec.com with "subscribe isn".
Today's ISN Sponsor: Repent Security Incorporated [www.repsec.com]
Received on Tue Dec 8 08:59:47 1998