Bug #74942 Export rows are limited by number of review rows
Submitted: 20 Nov 2014 17:58 Modified: 25 Feb 2015 20:05
Reporter: Kerry Marsh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL for Windows: MySQL for Excel Severity:S3 (Non-critical)
Version:1.3.3 OS:Microsoft Windows (7)
Assigned to: CPU Architecture:Any

[20 Nov 2014 17:58] Kerry Marsh
Description:
I'm trying to use MySQL for Excel to export 35000 rows of data in my spreadsheet to MySQL. 

The exports appear to transact successfully - but when I check the exported data in workbench I find the exported table has only n-1 rows of data where n was the number of rows selected for preview and calculate datatype. When set to the default of 100 - I get 99 rows exported, when I set it to the maximum of 1000 I got 999 rows exported. 

I've tried reviewing the SQL script before export and have confirmed by experiment that if I specify n rows are to be used for preview and calculate datatype then it only generates n-1 rows of exported data. 

This feels like it's either : 
1. A limitation of MySQL for Excel - ie you can only use it for relatively small datasets. I'd be surprised if that were the case though. 
2. There's a bug that limits the export to the size of the preview. 

Should I be reporting this as a bug ? 

Kerry

How to repeat:
Take a largish dataset of more than 1000 rows of data in an Excel spreadsheet.
Open MySQL for Excel from the data tab.
Select options and then click 'Preview SQL statement before sending to server'. This doesn't affect the outcome but does enable you to see the outcome more easily.
Export the data to MySQL.
In the dialogue box for export select 'Advanced options'.
Type a number n (n = anything between 2 and 1000) in the 'Use the first n Excel datarows to preview and calculate datatypes.
Press 'Export data'
Check out the 'Review SQL script'
Notice it says Review of SQL script. Creating table xyz, inserting n-1 rows.
If you go look in MySQL you'll find only n-1 rows of data.

Suggested fix:
I have no idea.
If 999 rows of data is a limitation for MySQL for Excel - it's a very low limit.
[20 Nov 2014 18:41] Javier Treviño
Kerry, I verified this bug, sorry for the inconvenience, I replied to your forum post supplying a workaround.
[4 Dec 2014 3:16] Ryan Tanaka
I'm having the same issue on Windows 8, Excel 2014 as well.  Can someone provide me a link with the workaround?
[4 Dec 2014 16:27] Javier Treviño
The only workaround (though a bit "ugly") would be to go to the Advanced Options in the Export Data dialog and increase the preview rows up to the number of rows you intend to Export.

You can subscribe to this bug to get a notification when it gets fixed.
[4 Dec 2014 19:44] Ryan Tanaka
That method doesn't seem to work -- the options menu caps out at 1000 also.  It might be related to the limitations that MySQL puts on requests?

I'll stay subscribed to this thread for now...hope it gets fixed soon!
[4 Dec 2014 21:25] Javier Treviño
Understood Ryan, then you will need to export a few (creating the table) and use Append Data to append the rest to the created table.  Sorry about the inconvenience, we will give this fix a high priority.

Regards.
[10 Dec 2014 22:49] Michael DaPrato
A workaround:
on the button that says "Export Data" -hit the drop-down triangle, and switch it to "Create Table".

When you are done setting up you columns, hit the "Create Table".  It will create the table. To refresh the schema hit <Back and select a different schema, then back and reselect the same schema.  You should now see the new table.  Select the new table.  Then you can select the "Append Excel Data to Table" option, which doesn't seem to have the limitation.
[14 Dec 2014 0:12] Dan Dan
This bug is also affecting me (Win7 SP1, Excel 2010). Anxiously awaiting a permanent fix! :)
[30 Dec 2014 18:48] Javier Treviño
Posted by developer:
 
Fixed a bug that occurred during the preparation of data to be exported to a new table, when the number of rows to be exported was higher than the number of previewed rows, the Export Data operation was restricted to the number of previewed rows.
[18 Jan 2015 20:52] Yan Ge
So has be bug fix been released yet?
[23 Jan 2015 22:02] Javier Treviño
It's been fixed, this will ship with the release of MySQL for Excel 1.3.4.
[23 Jan 2015 22:03] Ryan Tanaka
Cool, thanks for getting it fixed!
[25 Feb 2015 20:05] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL for Excel 1.3.4 release, and here's the changelog entry:

During "Data Export", if the number of rows to be exported was higher than
the number of previewed rows, the export operation was restricted to the
number of previewed rows. 

A workaround was to increase the number of previewed rows to the number 
of rows being exported.

Thank you for the bug report.