MySQL Forums
Forum List  »  MySQL for Excel

MySQL Excel Ad-In Column Mapping Issues
Posted by: Su Laus
Date: April 03, 2015 01:27PM

Configuration: Windows 8.1, Excel 2013 – 64bit, MySQL for Excel 1.3.3

The MySQL for Exel is an excellent tool for those working with Excel and should be further developed. Nevertheless, there are some issues.

When “Append Exel Data to Table”, the mapping of columns from Exel to the MySQL-table has several issues. I always worked with “First Row Contains Column Names” and the Excel column names and even sequence are identical (blanks replaced with “_”) of the MySQL table, because I have imported the table from that Excel sheet firstly. The first column is formatted as Excel-Date (dd.mm.yyyy). That column had the most problems. The second column was Excel-Time (hh:mm:ss).

a.) It is a little cumbersome, that the setting of the “First Row Contains Column Names” checkbox cannot be saved and is always unchecked for every append. An option for that would be a good idea.

b.) The automatic mapping method does often fail, so I tried to store the manually applied mapping. When I applied that stored mapping scheme as "Mapping Method", it does not work. Apparently, the automatic mapping was executed at the end of storing or at the end of applying. However, the “Date” column was always unmapped.

c.) I upgraded to MySQL for Excel 1.3.4, but there, the automatic column mapping was even worse, because the first column was always assigned to a lot of columns. My impression was, that there were even more issues, so I went back to version 1.3.3.

d.) The behaviour strongly depends on the setting of “Use the first x Excel data rows to preview and calculate data types". When I set x=1, so only the first row with column names is evaluated, the automatic assignment worked and also the stored mapping scheme.
When I set x>1, the automatic mapping had issues. For the setting with x>1 and using the stored mapping, the same result is achieved as if I use the automatic mapping. (see topic b.)).

e.) There should be two independent options on how many Excel rows are displayed as preview and how many Excel rows are taken for calculating data types.

f.) Furthermore, I do not understand, why the first column with the fitting column names does not have precedence over the parameter type based column mapping. It seems to me that data type issues and column mapping is too strongly coupled.

g.) I cannot understand, why a fixed column mapping (first Excel column to SQL-column-A, second Excel column to SQL-column-B, etc.) cannot be saved as manually assigned.

h.) I tried to format the Excel-Date and Excel-Time as Excel Text and disabled “use formatted values”. I could append that correctly to SQL-date and SQL-time fields. Nevertheless, the column mapping with first row column names does not assign the date and time columns if x>1 is set.

--- Summarizing: ---
The main issue is about the storage / application of manually performed column mappings, which does not work.
The second issue is about the column mapping of date and time (mainly date) columns either in Excel-Date/-Time format or even as Excel-Text format.

By the way, see also Bug #76517 Column mapping not saving/loading correctly (among other issues).

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Excel Ad-In Column Mapping Issues
3259
April 03, 2015 01:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.