It is known that many organizations and individuals use MS Excel to visualize data and to do arithmetic or statistic operations over it. The main disadvantage of the product is it cannot handle even medium size tables (a few millions of records). In view of this fact many companies consider to migrate MS Excel spreadsheets into a more powerful database management system as far as data volume grows.
Most of modern popular DBMS provide many benefits towards MS Excel in storing, protecting and managing the data. However, if organization does not plan to increase total cost of ownership for new data warehouse, they should only choose a free database management system such as MySQL.
Introduction to Migration
The most transparent way of data migration from MS Excel to MySQL consists of the following steps:
- export Microsoft Excel data into plain text or comma separate values (CSV) format
- create empty MySQL table complied with MS Excel data to migrate
- use MySQL “LOAD DATA” command to import data from intermediate storage into created table.
This approach requires some knowledge in database administration, since all data types of new MySQL table must be specified properly. Otherwise, the conversion procedure may lead to data loss or corruption. Users having no strong skills in MySQL should consider another option of migration.
MS Excel to MySQL migration tools
There are some dedicated tools to automate migration data from MS Excel to MySQL. This software is able to run the entire conversion process with minimum human efforts. One of such products is MS Excel to MySQL converter by Intelligent Converters software vendor. It has user-friendly interface and provides enough capabilities to migrate any MS Excel spreadsheets. The resulting MySQL database is very accurate because the converter does all necessary data transformation, handles different character sets and handles all other possible bottlenecks of the migration process.
Smart types mapping
The main challenge of the migration procedure is MS Excel has just a few data types while MySQL has much more types. For example, MS Excel has one numeric type instead of separate types like INTEGER and DOUBLE. Due to this fact all MS Excel numbers are converted into MySQL DOUBLE in order to avoid data loss. According to this approach the resulting data may have rounding issues. As a workaround for this issue Excel to MySQL converter is recognizing each value of every column during the migration process. If all values of some column allow setting more precise type, the tool refines it.
Smart types mapping technique gives even better results when extracting data from Comma Separated Values (CSV) files. All CSV values are initially treated as text. After scanning all the data,MS Excel to MySQL converter can transform particular column to numbers and dates.
More details on MS Excel to MySQL converter can be found at the official product page.
