Home > Documentation > MS Excel to MySQL
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.