On Wed, Jan 16, 2013 at 10:48 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:
What I've done is to write my own utility (Thanks for the source,
Scott!) and I don't rely on what Excel reports, I rely on what I
actually see in the spreadsheet. I use the same sort of select...endsl
group to decode the contents of the cell, but then depending on the
column, I push that value into the real datatype.
You're taking one property of Excel (that the same column can have
cells of multiple types) and apparently concluding that Excel is
somehow unreliable about reporting its types.
Excel is absolutely reliable about reporting its types. The "problem"
is that spreadsheets are not (conventional) databases. Database
tables have a limitation (all "cells" in a column have the same type)
that spreadsheets, by design, do not have. This is often a *useful*
limitation, in the same way that static typing affords a compiler the
chance to do certain kinds of error-checking and optimization. But
spreadsheets are not (and should not be!) bound by it.
So I don't really understand what you mean by "relying on what you
actually see". If Excel has a cell that *it reports* as text, whose
value is '123', then what do you actually see, exactly?
Or are you talking about how to handle the case where multiple types
are present in the same column? If you are coercing/casting all the
values in a column to some predetermined type (which is necessary for
importing the data into a database table), then it's not the fault or
responsibility of Excel to somehow know the type you want.
The Excel file format is actually much stricter and more reliable than
people seem to think. Certainly it is stricter and more reliable than
CSV, unless you control both the construction of the file and the
interpretation of the data within it (in which case Excel, CSV, and
whatever else you care to come up with are all equally reliable
formats).
The Excel *parser*, whose job it is to interpret data loaded from CSV,
is the thing that really drives everyone nuts. This has nothing to do
with the Excel file format, though.
John
midrange.com
