max_ny wrote on 12.01.2006 23:11:

There is no way you can do it 100%. Any character sequence you read
could be mapped to several data types.

Take the sequence "20060112". This could be a VARCHAR column that
happens to have no characters (only numbers), or it could be an integer
value, or it could be a float value that happens to have no decimal
digits, or it could be a date.

I don't see a different solution then to let your user provide the
necessary information.

Thomas

max_ny wrote On 01/12/06 17:11,:

   There is no foolproof way.  Fundamentally, all you
get from the file is a bunch of Strings that are the
external appearance of data fields.  You're trying to
deduce the original data by gazing at its appearance,
and your problem is that there may be many different
internal data items that could produce the same String.

   "4" - It looks like a number, but is it an integer
         or a floating-point value?  Is it a percentage
         that really represents 0.04?  Or maybe it's
         part of a date: do the nearby columns contain
         7 and 1776?

   "F" - It might be a short string, as in F Murray
         Abraham.  Then again, it might be the Boolean
         value "false."

   "1:10" - Very likely a time, but what kind?  If it
         denotes an interval, does it mean 70 seconds
         or 70 minutes?  If it denotes a specific time
         of day, does it mean early in the morning or
         early in the afternoon?  Then again, it might
         be the odds your bookie is quoting.

   "1/2" - If a date, is it January 2 or February 1,
         and in what year?  Or is it just 0.5 (does
         the next column say "tsp")?

   The very best way to resolve such ambiguities is to
get some outside authority (e.g., the user) to tell you
what the columns contain.  Failing that, a reasonable
approach might be to take a sample of fifty or a hundred
rows, and try several ways of "understanding" the set of
values you find in each column: If one of them works
more often than the others, guess that it's the right
interpretation.  (It may be a good idea to ignore the
first several rows; they might be column headings from
a spreadsheet or something of the kind.)

   For example, if the values found in column 3 are

    Quantity, "", 42, 29, 3001, 86, 107, 1336

you might well decide that the first two rows are titles
and the real data consists of integers in rows 3 and on.
(They could also be floating-point values or percentages,
of course, but a reasonable call might be to prefer the
"simpler" explanation.)  On the other hand, if you found

    Exchange, Rate, 1.0035, 0.9926, 1, 99.4217

it would be reasonable to assume you're looking at a
column of floating-point numbers plus two rows of titles.
Yes, one of the values would also work as an integer
(it's telling you how many gold splonders you can get for
one gold splonder), but floating-point successfully
"explains" more of the values and so is more likely to
characterize the whole column.

   Similarly, an entire column of T and F or of Y and N
is probably a column of Booleans; if A and R and L and
S are also mixed in, it's probably a column of strings.
A column consisting entirely of integers in the range 1..12
may well be month numbers; 1..31 might mean day numbers.
Maybe you're lucky and there's a leading "$" or trailing
"%" to give you a hint (careful of those dollars, though:
are they American, Canadian, Australian, or Doodledorfian?).
Start with a set of "value interpreters" of various kinds,
apply each of them to all the values in a column, and score
each by how many times it's able to make sense of what it
finds.

   It's going to be an imprecise business ...

This is similar to the logic in my Align utility, and also in the HTML
table sorter,  that scan a file first to determine if each column is
alpha or numeric.  If all entries are numeric, I right align or sort
numerically.

So you do a pass through the file with CSVReader  (see
http://mindprod.com/jgloss/csv.html

There is an option to give you each entire line as an array of
strings.

Examine  each string, and adjust your types[col] array.

Now you know the types of each column.  You can then read the file
converting each entry to the column type and know it will work.

You could use String.matches(r) with r being a regular expression.
For example

if (col.matches("[0-10]")) { type = integer }
else if (col.matches("[0-10]\.{0-10}+")) { type = float }
else if (col.matches("(true)(false)")) { type = bool }
else if (col.matches("[0-31] [(jan)(feb)...] [0-2034]")) { type = date }
and so on...

(not sure about the regexp syntax, just to show how you could do it)

Francesco