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. When comparing those two DBMS, PostgreSQL is more sophisticated, takes more time to learn and more resources for development. If the company does not need to deploy complicated corporate-scale data warehouse, they should migrate from Microsoft Excel to MySQL.
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. The next step is to create MySQL table having structure suitable for exported data. Finally, the data from intermediate files is imported into MySQL tables through “LOAD DATA” statement.
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
One of the tools to automate migration 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 with just a few mouse clicks. 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 supports much less data types than MySQL. For example, it does not support such numeric types as SMALLINT, INT, FLOAT and DOUBLE. In view of this fact all MS Excel numbers are initially converted into MySQL DOUBLE to preserve precision. This method may generate inaccurate data in the target tables. To refine results of migration, Excel to MySQL converter analyzes values of each column. 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:
1.000000000000000e+000
3.000000000000000e+001
2.000000000000000e+002
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.