MS Excel is known as the most popular product to create visualize, analyze and manipulate the data applying arithmetic or statistic operations. Wide range of companies use the tool to store sales records, client data, etc. However, when company grows it may need to scale the data management system to get more capacities and to store data in safe manner. For this purpose one of the modern database management systems (DBMS) can be used.
When planning data migration to another system it is reasonable to consider related benefits.All modern DBMS provide enough capabilities to store, manage and share data. However, if company does not plan increasing TCO for new system, they should consider only open source database management systems such as MySQL and PostgreSQL. To make right choice between these two DBMS, it is important to know all cons and pros for each system. PostgreSQL is more complicated than MySQL, so it can be efficiently used for large corporate data warehouses and at the same time it requires more resources for development than MySQL. If an organization does not have strong database development team should consider MySQL as the most suitable target for migration from Microsoft Excel.
Migration strategies
There are multiple approaches to migration data from Microsoft Excel to MySQL.Straight forward way requires exporting data from MS Excel spreadsheet into comma separated values (CSV) file, creating empty MySQL table and importing data to the target MySQL table via “LOAD DATA” command.
It may besuitable for experienced users or database administrators, since this solution requires manual creation of destination MySQL table and all data types must be specified properly. Otherwise, the migration may end up with data loss or corruption.
Data migration software
Another option to import data from MS Excel spreadsheet to MySQL database is using special tools for automation of the conversion process. Excel to MySQL converter is an application for this purpose developed by Intelligent Converters, a software vendor specializing in data migration field since 2001. The tool has user-friendly interface and provides enough capabilities to migrate large and complicated MS Excel spreadsheets. The application transforms data when it is necessary, supports Unicode and accepts command line arguments in order to script and to schedule conversion process. Excel-to-MySQL converter can also export data into MySQL script file for those cases when MySQL server does not allow direct connection.
Intelligent types mapping
Unlike Microsoft Excel that provides just a few data types, set of MySQL types is much more comprehensive. For example, MS Excel does not distinguish INTEGER and DOUBLE types so all numeric data is converted into MySQL DOUBLE in order to avoid data loss. This approach causes inaccurate look of the resulting MySQL data.
In order to resolve this issue Excel to MySQL converter collect statistics for each column during the conversion process. If all values in some column can be casted to more accurate type, the program updates the type for that column.This technique of refining data types can be illustrated on the following example. Assume, there is a column in MS Excel worksheet with these values:
10.000000000000000e+000
-3.000000000000000e+001
0.2100000000000000e+002
…
First, Excel to MySQL converter creates the corresponding MySQL column of type DOUBLE. While migrating data the program determines that all values of this column are integers.At the end of conversion process the application changes column type to INTEGER.
Intelligent types mapping technique provide seven better results when exporting data from Comma Separated Values format to MySQL because CSV files store all data in text format. After analyzing all the data Excel to MySQL converter can cast values of CSV fields to the most appropriate type such as numeric or date.
