Bug #78469 MySQL for Excel 1.3.4 column mapping problem
Submitted: 17 Sep 2015 12:11 Modified: 3 Feb 2016 20:59
Reporter: Kjell Eikland Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.3.4, 1.3.5 OS:Windows (10 64 bit)
Assigned to: CPU Architecture:Any

[17 Sep 2015 12:11] Kjell Eikland
Description:
I have been been using MySQL for Excel since 1.1 for a DB project for the past three years.  Even with some occasional issues, it has been generally reliable and fast under different Windows versions.  

Until a column mapping bug appeared in v1.3.4. I have seen that a similar bug was been reported more than half a year ago, but no fix has appeared.

Basically, automatic column mapping fails for all but the first column (always a text column).  The first column is also duplicated as map into a number of other columns.  The remaining columns are not mapped.  Manually fixing the mapping always works and data appends correctly. The stored mapping is always wrong, however.

Note that the column names and order are identical in Excel and in MySQL.  I can provide an create schema and excerpt of the excel file to demonstrate.

Hope you can look into this or provide some status.  A version with an option to force a 1:1 mapping when the field count is the same would in any case be of great help.

Best regards
Kjell

How to repeat:
Open addin, select connection, database and a table.
Mark entire Excel range (a "table") 
Select Append Excel Data to Table
Mark 1. row contains names and automatic mapping
The problem is directly visible (I can send a screen clip).
[17 Sep 2015 17:27] MySQL Verification Team
Please attach a screenshot of the issue. Thanks.
[17 Sep 2015 17:55] Kjell Eikland
As requested, here is the screenshot.  Hope it helps.  I can also create an excerpt of the Excel file if you wish also.

Best regards
Kjell
[17 Sep 2015 19:59] Kjell Eikland
Hello,
Here is the table create statement also (with dummy foreign keys).  I have verified that the issue exists with the Excel file sample sent a few minutes ago.  Hope this will help.

Kjell

CREATE TABLE `contracts` (
  `Site` varchar(30) NOT NULL,
  `Project` varchar(30) NOT NULL DEFAULT '',
  `Company` varchar(12) NOT NULL DEFAULT '',
  `CapMTPA` float unsigned NOT NULL,
  `Tolling` tinyint(1) unsigned DEFAULT NULL,
  `DES` varchar(30) DEFAULT NULL,
  `StartDate` date NOT NULL DEFAULT '0000-00-00',
  `Term` decimal(5,2) unsigned DEFAULT NULL,
  `EndDate` date DEFAULT NULL,
  `Phase` tinyint(2) unsigned DEFAULT NULL,
  `ContractId` tinyint(2) unsigned NOT NULL DEFAULT '1',
  `PriceAlpha` float DEFAULT NULL,
  `PriceBeta` float DEFAULT NULL,
  `PriceIndex` varchar(12) DEFAULT NULL,
  `Seller` varchar(12) NOT NULL DEFAULT 'ZZPROJECT',
  `Comments` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Site`,`Project`,`Company`,`StartDate`,`ContractId`,`Seller`),
  KEY `lngcontractsDES_idx` (`DES`,`Seller`),
  KEY `lngcontractsCompany_fkey_idx` (`Company`,`Site`,`Project`),
  KEY `lngcontractsSeller_idx` (`Seller`,`Company`,`Site`),
  CONSTRAINT `lngcontractsCompany_fkey` FOREIGN KEY (`Company`) REFERENCES `lngfirms` (`TICKER`) ON UPDATE CASCADE,
  CONSTRAINT `lngcontractsDES_fkey` FOREIGN KEY (`DES`) REFERENCES `t2` (`Site`) ON UPDATE CASCADE,
  CONSTRAINT `lngcontractsSeller_fkey` FOREIGN KEY (`Seller`) REFERENCES `t3` (`TICKER`) ON UPDATE CASCADE,
  CONSTRAINT `lngcontractsSite_fkey` FOREIGN KEY (`Site`) REFERENCES `t4` (`Site`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1;
[2 Nov 2015 11:18] Chiranjeevi Battula
Hello  Kjell Eikland,

Thank you for your feedback and sample Excel file.
Verified this behavior on MS Excel 2013.

Thanks,
Chiranjeevi
[2 Nov 2015 11:20] Chiranjeevi Battula
screenshot.

Attachment: 78469.PNG (image/png, text), 88.64 KiB.

[2 Nov 2015 19:06] Javier Treviño
Hi Kjell,
Have you tried this in the latest released 1.3.5 version? I am pretty sure that you tested on version 1.3.4 and this was fixed in 1.3.5, can you please verify?

Thanks.
[2 Nov 2015 19:07] Javier Treviño
Chiran,
The screenshot you attached shows the correct behavior of the automatic mapping.  The product is mapping the columns on a 1:1 basis (1st - 1st, 2nd - 2nd, 3rd - 3rd, etc.) for columns that have "matching" data types.

If you click on the "First column contains column names" in your specific test, you should notice all columns are mapped. You can see what I am explaining in our documentation:
https://dev.mysql.com/doc/mysql-for-excel/en/mysql-for-excel-append.html

This bug should have not been set to a verified status.
[2 Nov 2015 21:36] Kjell Eikland
Hi Javier,
I installed v1.3.5 on Saturday and was not able to test a complete Excel-DB update cycle.  However, I found that the automatic mapping now seems correct with the new version!  

It is definitely seems to be a big step forward, and it may actually have solved the issue.  I expect to do a full-cycle test Tuesday and will revert with a status update then.

Thanks.

Best regards
Kjell
[2 Nov 2015 23:11] Javier Treviño
Sounds good Kjell, I will cancel this bug then.
Unfortunately there is another known bug we introduced in 1.3.5 regarding boolean data types... it is creating a problem with both Export and Append Data operations.

It is a different bug, that is why I will close this one. 
That other bug is already targeted for the next release of the add-in.

Thanks.
[2 Nov 2015 23:14] Javier Treviño
Posted by developer:
 
Duplicate of already fixed bug 21799500.
[3 Nov 2015 9:02] Kjell Eikland
Hi Javier,
Did some further testing and can confirm that the bug with boolean values.  

Two further observations: 

1) Columns are found to be varchar when the column is actually a series of empty values and integers.  Automatic mapping is then not done for these "inconsistent" columns.  While manual override seems to work, it does not "stick" when trying to save or use the mapping.

2) In addition, check-box activation of automatic mapping refuses to stick (check box untoggles by itself), but sticks when activating it via the dropbox.  (Incidentally, why have both?  Is there an additional relationship between the two I have not figured out?)

Anyway, with these issues still open, I have not been able to successfully complete data export even with 1.3.5, so I am glad a new version may be around the corner.

Best regards,
Kjell
[4 Nov 2015 16:43] Javier Treviño
Hi Kjell,
The issue #2 you describe is caused by the boolean bug, which we already know about, so it will be fixed along.

As for issue #1, I would need more specific steps, like for example, is the column with blanks and integers (treated as varchar) the first column of the source grid? Are you trying to map it automatically or manually? Against which type of column is being mapped?  If you can consistently reproduce this #1 issue, it would greatly help if you raise a bug report for it, with steps to reproduce as specific as the ones you provided for this bug report, if you can provide also the script to create the target table and a sample .xls file it would be even better.

Thanks.
[3 Feb 2016 20:59] Javier Treviño
Duplicate of http://bugs.mysql.com/bug.php?id=76517