Microsoft Excel is the world leading tool for visual representation of the data and arithmetic or statistic operations over it. Many organizations use the product to store operational data like sales records, clients, etc. However, growing volume of the data may require more powerful and reliable database management system someday.
When considering target DBMS, it is reasonable to review main benefits of new system towards MS Excel. Most of modern popular databases provide wide range of features to store, manage, protect and share data. If company does not plan to increase TCO for new system, only free database management systems like MySQL or PostgreSQL should be considered. PostgreSQL is more sophisticated than MySQL and consequently requires more time to learn it and more resources for development. If an organization does not have qualified stuff and has no plans to deploy complicated corporate-scale data warehouse, they should choose MySQL as the most suitable target of migration from Microsoft Excel.
Strategies of migration
The easiest to understand method of migrating MS Excel spreadsheet into MySQL database is based on so called extract-transform-load technique. First step of this approach is to export Microsoft Excel data into plain text or CSV. Then it is required to create empty MySQL table with structure complied to the exported data. Finally, import data from intermediate plain text or CSV files to MySQL tables using “LOAD DATA” command.
This method may fit well for experienced database engineers or administrators while other users may experience some issues with it. It is vital to properly specify all data types in manually created MySQL tables, otherwise the conversion procedure may cause data loss or corruption.
Data migration tools
The simplest approach to MS Excel to MySQL migration is to use special software than can automate the entire procedure with just a few mouse clicks. One of such tools is Excel to MySQL converter developed by Intelligent Converters software company. Having easy-to-use interface, the product provides enough capabilities to migrate large and complicated Microsoft Excel spreadsheets. Excel to MySQL converter does all necessary types mapping and data transformations to produce the most accurate result. It can export data into MySQL script file for those cases when direct connection to MySQL server is not available.
Intelligent types conversion
Microsoft Excel has just a few data types, much less than MySQL. For example, it does not support wide set of numeric types such as SMALLINT, INT, FLOAT and DOUBLE. In view of this fact all MS Excel numbers are initially converted into MySQL DOUBLE to preserve precision. However, that approach may generate inaccurate result for particular data. To resolve this issue, Excel to MySQL converter analyzes each value in every column during migration. If all values in some column allow refined type conversion (for example INTEGER instead of DOUBLE), the program updates the column with new type.
To illustrate this technique, assume that MS Excel worksheet has column with the following values:
At the begin of migration MS Excel to MySQL converter creates the corresponding MySQL column as DOUBLE. Later the program recognizes that all values are integers and changes column type correspondingly at the end of migration.