Intelligent types mapping for Excel to MySQL converter
Unlike MySQL, MS Excel has just a few data types. For example, there is no
separate INTEGER and DOUBLE types. In view of this fact all MS Excel numbers
are converted in MySQL DOUBLE type to avoid data loss. To make the type
conversion in more intelligent way Excel-to-MySQL analyzes each value in every
column during the conversion process. If all values in a column allow more
precise type mapping, the program changes 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
...
At the begin of conversion process Excel-to-MySQL creates the corresponding
column in MySQL database with DOUBLE type. While migrating data the program
will find out that all values in this column are integers and will change column
type to INT at the end of the conversion process.
This algorithm gives the most valuable 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-MySQL converter will be able
to extract numbers and dates from CSV fields. For example your CSV file
stores dates in one of columns. When migrating CSV data to MySQL server (or
converting into MySQL dump file if the appropriate option is selected),
Excel-to-MySQL is trying to interpret every field as date using both
Windows API functions and its own patterns. The converter distinguish
datetime, "pure" date and "pure" time types, so at the end of
conversion each column is casted to the most appropriate type. If some column
contains mixed dates (i.e. "pure" date and "pure" time
values), Excel-to-MySQL keeps it as string to avoid data loss.
