MySQL Bugs: #74942: Export rows are limited by number of review rows

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:	Windows (7)
Assigned to:		CPU Architecture:	Any

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.

Kerry, I verified this bug, sorry for the inconvenience, I replied to your forum post supplying a workaround.

I'm having the same issue on Windows 8, Excel 2014 as well.  Can someone provide me a link with the workaround?

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.

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!

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.

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.

This bug is also affecting me (Win7 SP1, Excel 2010). Anxiously awaiting a permanent fix! :)

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.

So has be bug fix been released yet?

It's been fixed, this will ship with the release of MySQL for Excel 1.3.4.

Cool, thanks for getting it fixed!

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.