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:

1.000000000000000e+000
2.000000000000000e+001
3.000000000000000e+002
...

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.