Append results of a loop into the same worksheet

Toad Data Point
1

Hello

I am using a loop to export results and at present I end up with three results files, SalesTW.csv, Sales LW.csv and SalesLY.csv.

These files need to end up in a spreadsheet on a single tab called DATA, clearing whatever was in that tab previously. Now I can do this with a vb script but I was wondering if TDA can handle this?

I have just experimented with using the export to Excel file and the various options in there, which I have got to clear down the DATA worksheet and then insert the files but as it loops round the previous export is cleared, so when its finish all I end up with is the last file to be exported in the DATA tab, i.e. only a third of my output.

Is anyone able to advise?

Thank you
Regards
Glenn

2

I would suggest separating this into three exports to the same file. Something like this.
Export 1: Clear Data worksheet and export to specific Cell of named worksheet.

Export 2: Export to same file to named worksheet into specific cell.

Export 3: Export to same file to named worksheet into specific cell.

See attached sample.

Debbie
MultipleDataSheet.zip (8.65 KB)

3

Hi Debbie

Yes I see what you are saying but I would have to set up three loops for this scenrio to work which seems like quite a bit of duplication. Also the place where my data goes on that sheet is a dynamically named range and so it would not pick these up if I had to input them with spaces, as over the seasons the data in each file will grow.

Instead I opted to use the Import wizard and put the three files into a table in my database and then export them in one go using the export wizard, which works great.

Thanks
Glenn

4

That’s a clever idea. I hadn’t thought about that. That makes it one step to export all three tables to separate worksheets with the name of the tables. Did you get the data all into one sheet like you originally wanted?

I am currently working on making some automation activities accept multiple tasks. IE: Set Variable, Find and replace, etc. I would be interested in looking at your entire script to see if what I am doing might help in someway. Can you send me your script when you have it they way you want it?

Debbie

5

Hi Debbie

Yes I did manage to get all the three exports into the one tab in one go using this method.

I have attached all the relevant files associated with it.

Thanks
Glenn
Seasonal.zip (10.5 KB)

6

Excellent! This script is perfect to excercise the enhancements we are working on. We are also adding to import theability to import multiple files to the same target without having to define the target for each file.

I only have one comment. I don’t think you need to use the parrallel activity with the additional connections to Oracle and Sybase. You are executing a cross-platform query using the ToadSampleDatabase and that should be all you need. Try disabling those two connection activities and it should work fine.

Debbie

7

Hi Debbie

You are quite right, it does work if I remove these. I got into a habit of doing it as a workaround from version 2.1 (I think) and have been continuing to do so ever since.

I shall make sure I only use the Toad Sample database going forward, thank you.

Regards
Glenn