Intelligent types mapping for Excel to Oracle converter
Unlike Oracle, MS Excel has just a few data types and CSV format does not
have types at all. In view of this fact straight conversion may give quite
inaccurate results. That's why Excel-to-Oracle implements intelligent types
mapping algorithm. The main idea of this algorithm is to collect information
about type of each cell being converted and then refine types at the
end of conversion process.
This algorithm gives the best results on Comma Separated Values
(CSV) files. By default all values from CSV files are converted as strings.
However, after analyzing the data Excel-to-Oracle converter will be able
to cast certain columns to numbers or dates. Assume that your CSV file
contains dates in one of columns. When migrating CSV data to Oracle server,
Excel-to-Oracle is trying to interpret every field as date using both
Oracle API (OCI) functions and its own patterns. The converter distinguish
datetime, "pure" date (like 11/20/2009) and "pure" time
types (like 17:05:21), so each column will be casted to the most appropriate
type at the end of conversion. If some column contains both "pure"
date and "pure" time values, Excel-to-Oracle will convert it into
string to avoid data loss.
Intelligent types mapping works for converting from MS Excel spreadsheet as well.
If all values in a column allow more precise type mapping, Excel-to-Oracle
will change the column type at the end of conversion process. For example
some MS Excel column contains the following values:
MS Excel does not distinguish INTEGER and DOUBLE types. At the begin of
conversion process Excel-to-Oracle creates the corresponding column in Oracle
with NUMBER type that is equal to "double" to avoid data loss.
While migrating data the converter will find out that all values in this column are
integers and will change column type to NUMBER(11) that is equal to
"integer" at the end of the conversion process.