TDP Feature Fix/Request

Toad Data Point
1

Toad Data Point 3.3.0.1526

In previous versions of TDP - including, I’m fairly certain, earlier beta builds of TDP 3.3 - when using the “Select to File” activity, and choosing an Excel file, it was possible to specify the name of the tab in the Excel file without having to use an Export Template. This was incredibly useful, as it allowed for reports with multiple tabs to be built - each activity exported to the same file, and you could change the name of the tab from the default “Sheet n” to whatever you wished, and even had the ability to use variables from the automation script as part of the tab name!

This was wonderful, awesome! And then TDP 3.3.0.1526 came along and ruined everything.

In TDP 3.3.0.1526, the only way to get this precise functionality is to use an Export Template instead of the “Select to File” activity. However, Export Templates tend to be a bit hit-or-miss when it comes to using variables for file names, etc. There are some other issues with Export Templates as well, but they’re not the subject of this post.

Edited to Add: I should’ve tested to make sure that the Export Template supports the same functionality for more than one activity - it does not. I haven’t tested whether more than one Export Template going to the same file will work, but that’s not really the point.

However, I’ve discovered that if you point two or more “Select to File” activities at the same Excel file, they will continue to add new tabs for each activity, but with the default “Sheet n” naming convention.

I would really like to see the previous functionality where the name for the tab could be defined in the “Select to File” activity be restored. It doesn’t make sense to me why it was removed in the first place, and it does seem to violate the - admittedly general - cardinal rule of software development that each successive version should fix the bugs of the past and add additional features - not take away or degrade existing features.

2

When we added the Automation Tutorial Wizard we realized that the attributes on this Select To File page were pretty confusing. So we made the selection simpler
since this control was taken from the Export Wizard.

This change was to help make automation easier to understand for new users. Only a very small percentage of users use automation. When asked why, it was because
they thought it was too hard to learn how to use.

So, unfortunately, the ‘simplification’ has turned out for you to be lessoned functionality. I’d rather try to fix any issues you have with the export wizard.
Or perhaps we could consider a simple and advance option for the select to file.

I entered CR111619 for this enhancement.

Debbie

From: N.B. [mailto:bounce-NB@toadworld.com]

Sent: Wednesday, August 07, 2013 12:36 PM

To: toaddatapoint@toadworld.com

Subject: [Toad Data Point - Discussion Forum] TDP Feature Fix/Request

TDP Feature Fix/Request

Thread created by N.B.

Toad Data Point 3.3.0.1526

In previous versions of TDP - including, I’m fairly certain, earlier beta builds of TDP 3.3 - when using the “Select to File” activity, and choosing an Excel file, it was possible to specify
the name of the tab in the Excel file without having to use an Export Template. This was incredibly useful, as it allowed for reports with multiple tabs to be built - each activity exported to the same file, and you could change the name of the tab from the
default “Sheet n” to whatever you wished, and even had the ability to use variables from the automation script as part of the tab name!

This was wonderful, awesome! And then TDP 3.3.0.1526 came along and ruined everything.

In TDP 3.3.0.1526, the only way to get this
precise functionality is to use an Export Template instead of the “Select to File” activity. However, Export Templates tend to be a bit hit-or-miss when it comes to using
variables for file names, etc. There are some other issues with Export Templates as well, but they’re not the subject of this post.

However, I’ve discovered that if you point two or more “Select to File” activities at the same Excel file, they will continue to add new tabs for each activity, but with the default “Sheet
n” naming convention.

I would really like to see the previous functionality where the name for the tab could be defined in the “Select to File” activity be restored. It doesn’t make sense to me why it was removed
in the first place, and it does seem to violate the - admittedly general - cardinal rule of software development that each successive version should fix the bugs of the past and add additional features - not take away or degrade existing features.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

3

Either or, though I think the Export Templates need a pretty exhaustive overhaul, at least when they’re used in conjunction with Automation. I think the easier thing, from a user perspective, would be to add a Simple vs. Advanced option for the Select to File.

4

I agree. Since I already have automation files that generate Excel files with multiple named tabs, I’ll either have to do some major oerhaul to those processes or revert back to Toad 3.2. I was excited about the possibility of being able to use Virtual Views/Toad Views in the 3.3 automation, but it seems like that may have to wait for me.

5

I need to understand. If you used the Select to FIle and set worksheet name before we still honor that. If you need to make a new export is it really that much of a hassle to change to using an Export Wizard template? We were counting on this not being a big deal. If it is, I just need to understand why it is a hassle. It maybe just that it is a change and we didnt’ tell you about it. But now that you know would you really return to TDP 3.2 instead of using the Export Wizard?

6

That's the problem - the change in TDP 3.3 is to forcing the use of an Export Template is not equivalent - see my edit to my original post, but in summary: Whereas the original "Select to File" activity would allow to not only name the tab what you wished - including the use of variables from the automation - it also allowed you to build multiple tabs in the same Excel file by simply exporting to the same file multiple times, just giving a different name for each tab.

When I attempted to replicate the same thing with an Export Template in TDP 3.3, it won't happen. It'll create the first tab, but then when it goes to export the next script to the same file - but with a different tab name - it'll actually delete the original tab and replace it with the data from the second activity. This is not equivalent functionality.

7

It is true that the export template does not support variables for the worksheet names. That is handy. But I tried the scenario you described and it worked for me. I had two export wizard templates. Each exporting to the same excel file but different worksheet. But the worksheet name was hard coded.

I did enter an enhancement request for adding an advance button that would pop up the old UI. I will see what we can do about scheduling this. This is QAT-556.

8

[quote user=“Debbie Peabody”]

It is true that the export template does not support variables for the worksheet names. That is handy. But I tried the scenario you described and it worked for me. I had two export wizard templates. Each exporting to the same excel file but different worksheet. But the worksheet name was hard coded.

I did enter an enhancement request for adding an advance button that would pop up the old UI. I will see what we can do about scheduling this. This is QAT-556.[/quote]

Using two Export Templates was the one scenario I hadn’t tried yet; unfortunately, without the ability to give custom names to the tabs (with or without variables), the functionality is till essentially useless because 99% of our multi-sheet reports are vastly different data from one tab to the next, and we need the ability to tell which tab is which.

9

I had a similar issue when importing my scripts to 3.3 from 3.2. I have many bundled automation scripts that produce daily output for many units of my company. Each day, each division gets their own Excel with a tab for each set of data… Many of these reports include a dashboard on the initial tab with pre-designed charts, etc. The inability to recreate this (or even the need to have to re-write the entire automation) has made me decide to revert back to 3.2 for any automation scripts I already have. The select to file is very straightforward and the ability to select not on the tab, but where on the tab to place data has allowed me to automate many things I could not before.

I also would like to see the previous functionality of “select to file” return.

10

Thanks for you input. I am looking into this.

11

Are we able to use variables at all in the export wizard? I have been unsuccessful using a variable in the filename of the Exported Excel file like we did in previous versions?

12

I think Toad Data Point is going in the wrong direction with select to file. It is simpler, easier to use and has some features the Export Wizard does not have, but it is not as robust as Export Wizard. When I take a job (.tas) file created by someone else in my group and schedule it on the server it is a piece of cake change the connection information on select to file. I have to completely recreate the Export Wizard in these cases (pain in the @$$). What I would rather have seen is bring txt file support with your choice of delimiter added to select 2 file so I don’t have to use the Export Wizard anymore. I want everything select 2 file could do in 3.2 plus what could be done in Export Wizard. Not less. Myself and other users here use variable tab naming in select to file. I cannot upgrade to 3.3 based on this issue. Please go back or at least put in an advanced tab like N.B. suggested.

13

I hear what you say and am working on getting back the Select to File feature. Unfortunately it will not be able to make the next release (TDP 3.4). i will add your comment to QAT-556 and try to push it up in priority.

You can however, change the connection an Export Wizard template uses without changing the template. Simply select and change the “Use Automation Connection”. This tells automation to use that connection instead of the one in the expert template.

14

Debbie,

The problem we have with using Export Templates is that they seem to inconsistently work with using TDP variables - specifically when attempting to use them with a filename and/or path.

What makes it even more problematic - and perhaps this is the root cause - is that the Export Template is usually built outside of the Automation creation process, and then linked in using that Task. It some ways, it’s almost as if the Export Template simply doesn’t have the variables “registered” with it at run time sometimes - but not always.

15

Did you happen to see what I put in this post?

www.toadworld.com/.../20390.aspx

If you are up to it you can manually, edit the tas file and get the Select to File options back. Not elegant but works.

BTW, I am talking to our product manager tomorrow about this feature and when she will allows us to put it back into the product. So i have been listening, it just takes time sometimes when we have to do an about face.

16

No I didn’t, and it broke the link when you posted it. I’ve manually edited the .tas files before because TDP can be really slow on complex scripts and would seemingly randomly change items I hadn’t touched and then break a bunch of things - so it was easier to just edit the XML directly :slight_smile:

17

P.S. We are working on getting this feature added back in TDP 3.4.

18

I’d really like to see the functionality of being able to create and write data to multiple tabs in a single workbook.

Currently (Toad 3.3.0.1526) the Export Wizard feature does not work for me. No matter what, I get “Object reference not set to an instance of an object.”

The same SQL script & settings work fine using the Select to File activity, but this forces me to make a whole bunch of individual workbooks and then merge them into one.

This particular year-end project has about 42 worksheets, and I’ll probably have to repeat it at the end of 2014, so it would be nice to be able to automate it.

Any advice on whether I can get past the “Object reference not set to an instance of an object” error in the Export Data activity?

I would be happy to edit the .tas file if I could get that functionality working, but Debbie’s link above to …20390 doesn’t work, and I couldn’t find the relevant post by inserting 20390 into the URL or by using the search feature.

19

I know your pain :frowning: I've got a monthly report that has some 64 Worksheets.

We've actually started using VBScript, Powershell and the Excel COM object model to automate a number of our reports due to the issues we keep encountering with TDP - this one, and another where we keep hitting an arbitrary available RAM limit that causes TDP to stop exporting an Excel file at 98%.

It does take longer to build out and test the automation's with VBScript/Powershell, but we have vastly more control over the entire process and the performance has been significantly better.

We'll continue to use TDP in the future as it does serve a purpose for quick and easy automation's, but beyond that we'll go in a difference direction.

20

This is the Import/Export Report:

12:33:08 PM Thread (4) Export Started [11/15/2013 12:33:08 PM]

12:33:08 PM Thread (4) Export using connection: DATABASE (USER), PASSWORD

12:33:08 PM Thread (4) Export Wizard error processing objects, export halted.

12:33:08 PM Thread (4) Error parsing Export template: Object reference not set to an instance of an object.

12:33:08 PM Thread (4) Export Finished [11/15/2013 12:33:08 PM]