Converters for Microsoft SQL Converters for MySQL Converters for IBM DB2 Converters for MS Access Converters for Adobe PDF Converters for DBase/FoxPro Converters for Microsoft Excel Converters for Oracle Converters for Postgres MSSQL-to-Excel Converter MSSQL-to-MySQL Converter Intelligent Converters Software Access-to-MySQL Converter
convert excel to oracle, excel to oracle converter, excel-to-oracle, xls2ora, excel2oracle, msexcel, command line, automate conversion, xls file, free download, shareware
FAQ  |  Site map  |  Contact Us
About Us How to Buy All Products Discounts Migration Service Support Become a Partner Articles

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.