Toad World® Forums

how to append an automated excel export to the same workbook tab???

hi all

i would like to know how can i append an export to excel to the same tab? i understand you can i append it to a workbook, but i would like to have continues data in one sheet

thx

We do no have a true append. We only offer exporting to a specific cell location.

If you are talking about from an editor, click on the cell where you want to do the export, and then right click from a grid and choose export to Excel Instance at active Cell.

If you are trying to do this in automation you need to export to a specific cell location.

Neither of these actions truly append. We do not know where the last data is so we don't know what row to start on.

Can I have two separate tasks export to specific cells in the same tab worksheet?

So one exports to cell A1 and the next start on A15

Magdiel Cruz (MA MDW)

From: Debbie Peabody [mailto:bounce-Debbie_Peabody@toadworld.com]
Sent: Friday, December 20, 2013 4:30 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] how to append an automated excel export to the same workbook tab???

RE: how to append an automated excel export to the same workbook tab???

Reply by Debbie Peabody

We do no have a true append. We only offer exporting to a specific cell location.

If you are talking about from an editor, click on the cell where you want to do the export, and then right click from a grid and choose export to Excel Instance at active Cell.

If you are trying to do this in automation you need to export to a specific cell location.

Neither of these actions truly append. We do not know where the last data is so we don’t know what row to start on.

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.

I am not sure if this can work but if you are using select to file in your job the row_Count variable will hold the number of rows added in the first query. If the second select to file will take a variable for the starting cell location, you can use the row count from the first select to file (plus 2 for header and next row) to set the append location dynamically in the second select to file. Never tried this, so good luck. Please post if it works. (use version 3.2 or 3.4 but not 3.3)

This works. thx

Magdiel Cruz (MA MDW)

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]
Sent: Monday, December 23, 2013 12:55 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] how to append an automated excel export to the same workbook tab???

RE: how to append an automated excel export to the same workbook tab???

Reply by GregDavis11009

I am not sure if this can work but if you are using select to file in your job the row_Count variable will hold the number of rows added in the first query. If the second select to file will take a variable for the starting cell location, you can use the row count from the first select to file (plus 2 for header and next row) to set the append location dynamically in the second select to file. Never tried this, so good luck. Please post if it works. (use version 3.2 or 3.4 but not 3.3)

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.

I got it to work, to the point were the next query would essentially overwrite any values on the starting point I give it but I need some more info.

How do I make the RowCount variable name field work to limit the amount of rows the sheet exports?

thx

Magdiel Cruz (MA MDW)

From: magdiel.cruz [mailto:bounce-magdielcruz@toadworld.com]
Sent: Monday, December 23, 2013 3:47 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] how to append an automated excel export to the same workbook tab???

RE: how to append an automated excel export to the same workbook tab???

Reply by magdiel.cruz

This works. thx

Magdiel Cruz (MA MDW)

Description: Description: Capture3

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]
Sent: Monday, December 23, 2013 12:55 PM
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] how to append an automated excel export to the same workbook tab???

RE: how to append an automated excel export to the same workbook tab???

Reply by GregDavis11009

I am not sure if this can work but if you are using select to file in your job the row_Count variable will hold the number of rows added in the first query. If the second select to file will take a variable for the starting cell location, you can use the row count from the first select to file (plus 2 for header and next row) to set the append location dynamically in the second select to file. Never tried this, so good luck. Please post if it works. (use version 3.2 or 3.4 but not 3.3)

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.

Description: Image removed by sender.

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.

I just played with the select to file and it does not take a variable in the staring row position, only a number that you type in (won’t take a # sign). Sorry. You can output two csv files and append them with a DOS batch command called from Toad. You use the Run Program system activity in Toad and put the path and batch file name on the Program line and in the Argument line you can put the file names to append and the output file name. The batch file (.bat file) would contain:

Copy %1 + %2 %3

Exit

I named this .bat file AppendFile.bat (goes on the Program line) %1 will be the 1st csv file on the Argument line followed by a space then the 2nd file name to be appended (without a header in the data) followed by a space and then the output file name. Note: you cannot have any spaces in the file names or the batch job will interpret it as separate variables. If you want to see the DOS window in action (for test purposes) put the word REM in front of the Exit command so the window will not close. Then you can open the file name passed to %3 with Excel with all your data in it.

Thanks Greg for the bat file technique. That is very nifty. I have entered QAT-1547 to add enhancement for appending rows.