Bug #73505 Cannot use values returned by Excel formulas in Append or Export Data operations
Submitted: 8 Aug 2014 1:57 Modified: 9 Oct 2014 7:21
Reporter: Javier Treviño Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S2 (Serious)
Version:1.2.0 OS:Any
Assigned to: CPU Architecture:Any

[8 Aug 2014 1:57] Javier Treviño
Description:
When formulas are used in Excel, the values returned by them are not recognized by MySQL for Excel to be used in Export or Append Data operations.
The Export/Append Data preview grids show blank or weird numeric values (-2146826265) instead of the actual value returned by the formula.

A possible (but ugly) workaround would be to copy the Excel range into another one and paste it as values only, then use the copied range in the Export/Append Data operation.

How to repeat:
1. Open MySQL for Excel.
2. Select any schema and table.
3. Prepare some data in Excel suitable to be appended to the selected table, or to be exported to a new MySQL table.
4. Replace some of the values of step 3 with formulas returning valid values to be appended/exported.
5. Select the whole Excel data to be appended or exported.
6. Click on Append Excel Data to Table or Export Excel Data to New Table.
7. Note in the preview grid the values returned by formulas are simply not recognized (they are blanks or -2146826265).
[28 Aug 2014 17:30] Javier Treviño
Posted by developer:
 
Fixed the code that pre-processes data for Export or Append operations to copy the Excel range's values and number formats instead of copying their formulas into a temporary hidden Excel worksheet.
[9 Oct 2014 7:21] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.3 release, and here's the changelog entry:

Values returned from Excel formulas were not recognized by MySQL for Excel
when used in Export or Append Data operations. The Export/Append Data
preview grids showed empty or unexpected numeric values (such as
-2146826265) instead of the actual value returned by the formulas.

Thank you for the bug report.
[7 Feb 2017 6:31] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=84851 marked as duplicate of this one.