Microsoft Excel is worldwide known spreadsheet that is perfect solution for visual representation and manipulations on the data. Many organizations store their data like sales records and clients contacts in MS Excel files. Unfortunately, this is not quite scalable solution since big data requires more powerful and reliable storage such as database management system (DBMS).
When planning migration from MS Excel to DBMS, it is important to recognize required advantages of new system over Microsoft Excel. Modern popular databases provide advanced capabilities to store, manage, protect and share the data. If an organization does not intend to increase cost of ownership for new system, it is reasonable to consider open-source products like MySQL or PostgreSQL. PostgreSQL is more sophisticated than MySQL and so it requires more efforts for administration and development. Therefore, if company does not have qualified stuff and has no need to create complex corporate-scale data warehouse, MySQL would be the most suitable target for migration from Microsoft Excel.
Options to Migrate MS Excel to MySQL
The most straight forward option of Excel to MySQL migration is implementation of extract-transform-load approach. First step is to export Microsoft Excel data to plain text or CSV. Second step is to create empty MySQL table with the appropriate structure accept MS Excel data. The final step is to import data from intermediate files into MySQL tables using “LOAD DATA” statement.
There are two possible challenges may be caused by this method. First, the load statement referred above cannot import large files that exceed ‘max_allowed_packet’ size of MySQL configuration. The workaround is to import data piecewise that can hardly be implemented following that approach.
Second, special symbols and foreign Unicode characters require special attention to avoid problems with importing. The workaround for this issue is to replace such symbols by the corresponding escape codes.
IT professionals may go fine with this option while other users may experience some technical issues with it. The main challenge is that all data types must be specified properly in composed MySQL tables, otherwise the conversion procedure may cause data loss or corruption.
Data Migration Tools
The simplest way to migrate MS Excel to MySQL is using software products than can automate the entire procedure with just a few mouse clicks. Excel to MySQL converter developed by Intelligent Converters software company is a tool having such facilities. It has easy-to-use interface and can migrate large and complicated Microsoft Excel spreadsheets. Key features of Excel to MySQL converter:
- Works with all version of MySQL running on Linux/Unix/Windows platforms including such forks as MariaDB and Percona
- Works with SAAS variations of MySQL such as Google Cloud SQL and Amazon RDS
- Supports all versions of Microsoft Excel
- Can export data from comma separate values (CSV) files
- Makes all required transformations to get the most accurate result
- Can merge MS Excel or CSV data into an existing MySQL tables provided that source and destination formats are conformed
- Supports Unicode
- Can export data into MySQL script file as workaround for those cases when there is no direct connection to MySQL server
Smart Types Mapping
Microsoft Excel has much less data types compared to MySQL. For example, it does not distinguish integer and decimal types. In view of this fact all MS Excel numbers are converted into MySQL DOUBLE to avoid losing data. However, this method may generate inaccurate results. To improve it, Excel to MySQL converter collects statistics for values in every column during migration. If all values or particular column allow better conversion (for example INTEGER instead of DOUBLE), the tool refines the column type.
Let us illustrate this technique on the following example. Assume, MS Excel worksheet includes such values:
1.000000000000000e+000
3.000000000000000e+001
2.000000000000000e+002
…
Initially MS Excel to MySQL converter migrates the data into DOUBLE column in the destination table. Later the program finds out that all values are of integer type and transforms it into 1, 20 and 300 correspondingly.
Table Structure Customization
Besides smart types mapping Excel-to-MySQL converter provides another option of dealing with unequal sets of data types in Microsoft Excel and MySQL – manual table structure customization. To access this feature, double click on the table name in “Selected worksheets” list of “Step 4 of 5” wizard page and enter the appropriate settings in the appeared dialog box:
To customize column’s attributes, select the item in the list and specify new name, type or default value using controls in “Field info” section.
