From Microsoft Excel to MySQL

MS Excel is one of the best tools to create visual representation of the data and to organize some manipulations like arithmetic or statistic operations. That’s why many companies choose this product to store some internal data like sales records, clients data, etc. However, as far as data volume grows they may need more powerful system to organize and store all data in safe manner. Database management systems (DBMS) can be used in such cases.

When choosing a database as migration goal it is reasonable to consider benefits of new system towards MS Excel. Most of modern popular DBMS provide strong capabilities to store, manage, protect and share data. However, if organization does not want to increase total cost of ownership for new system, they should limit scope of variants by free database management systems: MySQL or PostgreSQL. When comparing these two DBMS, it comes clear that PostgreSQL requires much more time to learn it than MySQL and consequently PostgreSQL-based systems take more resources for development. That’s why any organization having no qualified stuff in PostgreSQL field should consider MySQL as the best goal of migration from Microsoft Excel.

Migration strategies

There is straight forward way to migrate Microsoft Excel data to MySQL server. First, it is required to export data from MS Excel spreadsheet to plain text or CSV, then create empty MySQL table and finally use “LOAD DATA” command to import data from intermediate storage to MySQL table.

Is this approach enough? Yes, it is enough for mature programmers or database administrators. Since this solution requires manual creation of destination MySQL table, all data types must be specified properly. Otherwise, the conversion procedure may cause data loss or corruption.

Data migration tools

Another approach to migration data from MS Excel to MySQL is to use special software designed for automating the entire process. One of these tools is Excel to MySQL converter that has easy to use wizard style interface and provides enough capabilities for efficient migration of complex MS Excel spreadsheets. The result of migration is accurate at the same time since the application does all necessary data transformation, handles multibyte character sets and much more. To get more control over the migration process or in case there is no direct access to MySQL server, Excel-to-MySQL converter can export data into MySQL script file.

Intelligent types conversion

Microsoft Excel has just a few data types while set of MySQL types is much wider. For example, MS Excel does not have separate types INTEGER and DOUBLE. In order to avoid data loss all MS Excel numbers are converted into DOUBLE that generates inaccurate look of resulting data. As a solution of this issue, Excel-to-MySQL analyzes each value in every column during the conversion process. If all values of particular column allow more precise type conversion the program refines the column type.

Assume that some MS Excel worksheet contains the following values in particular column:

1.000000000000000e+000

3.000000000000000e+001

2.000000000000000e+002

At the begin of conversion Excel-to-MySQL creates the corresponding MySQL column as DOUBLE. During data migration, the program recognizes all values of this column are integers, then it will change column type to INT at the end of the process.

Intelligent types conversion gives even better results when migrating data from Comma Separated Values (CSV) files. All values from CSV files are initially treated as strings. After analyzing the data, Excel-to-MySQL converter can recognize numbers and dates in CSV fields.

 

Related posts