Data Point 3.8 export to Excel - number of rows per sheet

Toad Data Point
1

Hi,

I am in the process of migrating my Data Point automations which currently run on a desktop machine using Window 7 Professional to a server running SQL Server 2012 R2. The majority of automations create data exports in Excel .xlsx format.

My largest export currently contains over 130k rows and places them all in one sheet when using the Win7 machine. However, the export from the SQL Server machine is hitting the old excel limit of 65k on sheet1 and dumping the remainder in sheet2. Does anyone know how can I correct this issue to get the data back into one sheet?

Thanks

2

I believe that this is a function of the version of Excel you are using. What version were you running on the old machine versus what you have installed on the new machine?

3

Thanks, Mark, for the reply. The old machine has Excel 2010 and the new one does not have Excel installed. I considered this but there is no requirement noted that I could find in the Data Point installation or user guide. Additionally, it does export in Excel format with the .xlsx extension without Excel installed and it is readable in Excel on other machines so I am thinking this process is controlled elsewhere? Perhaps installing Excel on the SQL server machine would fix this but I don’t want to pay for an additional Excel license for this feature if I don’t have to.

Regards,

Paul

4

now this is pure speculation on my part and we will need to get the Product Team to chime in but I wonder if Toad is looking for an Excel instance on your local machine so it knows what the limits are and since it does not find Excel it defaults to the old default?

Again, we will need more input from the product team.

-Mark

5

Mark provided the correct explanation. In TDP 3.8 and early the default row limit is the same as in Excel 2003 - 65536. Now in the latest TDP 4.0 the default row limit is 1048576.

Regards

Aleksey

6

Thank You Aleksey.

The timing is perfect! I will install 4.0 in my new instance and try again.

Regards,

Paul

From: Aleksey Bazhenov [mailto:bounce-AlekseyBazhenov@toadworld.com]
Sent: Friday, July 29, 2016 3:42 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Data Point 3.8 export to Excel - number of rows per sheet

RE: Data Point 3.8 export to Excel - number of rows per sheet

Reply by Aleksey Bazhenov

Mark provided the correct explanation. In TDP 3.8 and early the default row limit is the same as in Excel 2003 - 65536. Now in the latest TDP 4.0 the default row limit is 1048576.

Regards

Aleksey

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point Forum notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

CAUTION: THIS MESSAGE ORIGINATED FROM THE PUBLIC INTERNET AND WAS NOT SENT FROM THE SMITHS EMAIL SYSTEM

PROPRIETARY: This e-mail contains proprietary information some or all of which may be legally privileged. It is intended for the recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the authority by replying to this e-mail. If you are not the intended recipient you must not use, disclose, distribute, copy, print, or rely on this e-mail.

7

Glad to help. Let me know if you have anymore questions after the upgrade.

-Mark

8

Hi,

I upgraded to Data Point 4.0 and am happy to report that the output is now all contained in one sheet.

Thank You for your replies and help!

Regards,

Paul