| Unlike popular relational databases, MS Excel has just | | | | datetime (like '04/08/1977 13:14:15'), "pure" date |
| a few data types and CSV does not store any | | | | (like '11/20/2009') and "pure" time types (like '17:05:21'), so |
| information about types at all. That's why straight | | | | each column will be casted to the most appropriate |
| conversion gives poor results. To improve this situation | | | | type at the end of conversion process. If the column |
| Intelligent Converters implement smart type mapping | | | | contains both "pure" date and "pure" time values, the |
| algorithm in export tools for MS Excel. The main idea | | | | program will convert it into string to avoid data loss. |
| of this algorithm is to collect information about type of | | | | Intelligent types mapping algorithm is also applied to |
| each field converted and then refine column types at | | | | direct migration from MS Excel spreadsheet. For |
| the end of conversion process. | | | | example MS Excel does not distinguish INTEGER and |
| This algorithm produces best result when converting | | | | DOUBLE types. In view of this fact all MS Excel |
| from Comma Separated Values (CSV) files. By | | | | numbers are initially converted into DOUBLE with |
| default all values from CSV files are converted as | | | | maximum precision to avoid data loss. |
| text. But after analyzing all data, Intelligent Converters | | | | Assume that some column of MS Excel spreadsheet |
| export tools for MS Excel will cast certain columns to | | | | contains the following values: |
| numbers or dates. The product is trying to interpret | | | | 1.000000000000000e+000 2.200000000000000e+001 |
| every field as date or number using both Windows | | | | 3.330000000000000e+002 |
| API functions and its own patterns. Once a column has | | | | During conversion process the program analyzes each |
| been failed to be treated as date or number, the | | | | value in that column to cast types in the most intelligent |
| converter just will skip it in next records to get better | | | | way. Since all values allow more precise type mapping, |
| performance of the conversion process. | | | | the converter will change the column type to INT at |
| Let's see how smart type mapping works on example. | | | | the end of conversion process. |
| Assume that source CSV file contains date values in | | | | Intelligent type mapping algorithm saves a lot of time |
| one of columns. While migrating CSV data to | | | | and allows automate export data from MS Excel |
| database the conversion tool tries to interpret every | | | | spreadsheets and Comma Separated Values (CSV) |
| field of the column as date. The converter distinguish | | | | files. |