Append Data to Same Excel or .CSV worksheet

Greg,

I get the idea here and this is something we’ve done similar before in VB to Microsoft Access Passthrough queries. Unfortunately, it appears that TDP doesn’t like the query logic embedded as text for the variable. For example if I was to put select ‘select distinct Account from List’ AS FIELD1 from Customer, then this scenario works and produces that SQL as the variable. But I get an error back when I try to use a criteria also in the field, such as 'select distinct Account from List Where Status = ‘A’ ’ AS FIELD1 from Customer.

The error I get back is that ‘’ is not valid. I’m sure that the criteria’s ’ and the ending ’ to make that a text field isn’t working well with it. Do you know a way around this issue? I can sure continue to try, but not sure how I get around this. I do know also Debbie has a fix entered for the variable not passing through the export wizard as well. Thanks for the idea, hopefully we can utilize it in the meantime until a fix is in place.

Chad

You may a have a single quote problem. Try this:

'select distinct Account from List Where Status = ‘‘A’’ ’ AS FIELD1 from Customer

you need to escape the single quote with two single quotes to get one single quote inside the string. The the Exec command should treat the ‘A’ as a literal string instead of seeing the 1st quote before the A as the end of the string to parse.

Hope that helps,

Greg, that does work and it makes sense. I’m at the point now where I want to place the Execute_1_SQL variable in as the query in the export wizard. I have to assume that the only thing you place in the query is the :Execute_1_SQL variable. When I do this, it prompts me for a value because its assuming its a bind variable. Even if I enter a small SQL query, it comes back with a schema read error. Therefore I can’t even get that variable to pass in the export wizard query part. Have you tried offhand? Are you able to make this work? I tried in both 3.4 and 3.6 Beta.

Chad

You lost me a little there. I tested a looped append to an Excel file with a variable for a table name and it works. The SQL I used was:

Exec('Select PrimKey, DataField, Otherfield, CreateDate From ’ + :Table_Name)

The Table_Name variable gets set each time in the loop (I made 3 tables; ‘TableA’, ‘TableB’ and TableC’ same structure but it does not have to be just the fields selected need to be the same datatype). I noticed that it puts the header row in again each time (so it has header, data, header, data, header, data). You would need two different exports to get around that, the 1st one with headers and the subsequent ones without.
Example_Script.tas (10.2 KB)

I did the post above in TDP 3.5 using the export wizzard.

Greg, I’m not sure how you got that Exec() function to work inside the query part of the Export Wizard. I cannot get it to work, because I think unlike a true execute script command that actually creates a variable for use, I wouldn’t know where to input the variable that execute created. I greatly appreciate the help with this, but I am starting to wonder if we aren’t creating a very complicated process to make a situation like this work that could be resolved hopefully much simpler with the fix that Debbie has entered hopefully allowing that variable to pass through similar to how the select to file does directly in the query portion of the export wizard. If so, this would be closely similar to how its done on the select to file and take a lot less steps and manipulation of the data utilizing execute functions to make entire lines of SQL as variables. I also worry on that because of the length and complexity of our queries and not sure if there’s a limitation in the first place on the length of a variable. Its normal for us to have 50 lines of query code Unioned to 10 different libraries that may not work in a situation like this. I will pursue working this more once we see if both Debbie’s proposed fix can make it much simpler or when they get to add the enhancement to the select to file to append to the excel file like the export wizard can.

Thanks again!

Chad

I’d like to follow up on this discussion. Debbie as you mentioned fix QAT-3203 was put in 3.6 and I’ve gotten the opportunity to test this and confirm that variables are now allowed in the .sql file from the loop in the export wizard task. I wanted to mention the steps that I had to take to get this operational and I’d like to get suggestions to see if you think this is the only way or the best way to accomplish what we are trying to do. Essentially we run out of many different libraries, therefore we utilize UNIONs to run queries for different libraries. We decided to use this loop function to feed 10 different libraries into the queries to run and export the file. With this new “append to file” functionality, we have the opportunity to loop that information into one file, extremely similar to how a UNION would work. So when I set this up, I created a table that stores the 10 different library names. I then set that up in the loop and the loop variable creates those library names as the variable. I then had to create a query first using a .sql file which had a single library (not the variable) to step through the export wizard to get it formatted to work (it would not allow the variable at this point). I then went back to the .sql file and put the unquote function inside with the loop variable name inside that for every table referenced with the library name. When I did this, it successfully runs through the query 10 times using 10 different libraries and appends to a single worksheet. Here’s the main and really only problem. You only have the option to say you don’t want header rows or you do want header rows. If you want the header rows, its going to create a header row for every different library it appends. If you do not want the header row, it will not create a header row at least at the top of the spreadsheet. The only solution that I thought to create was to create a select to file automation before the loop that has the identical query but the criteria essentially pulls nothing. This way it starts the sheet for you with a header row, then the looped query in the export wizard can append to that same sheet it started without header rows, effectively making it look like one large sheet without headers breaking each library up. I wanted to get your take to see if this was the way you envision accomplishing this and if you think its a very efficient way of doing things. Hopefully you have an understanding of what we are trying to accomplish and maybe this can help shed light on possible enhancements to the next version of TDP. Possibly an ability to set the header row only at the first variable in the loop, but when appending, do not add header rows to any of the other variables after the 1st in the loop, basically to have one clean spreadsheet with a single header and no other headers throughout the rest of the loops that the data appends to. Sorry for the BOOK, but wanted to make sure you understood where we’ve tested and are at currently with this option. Thanks!

Chad King

Chad, sounds like you are pretty much there. Try putting the 1st library outside the loop and include the headers. Then put the remaining 9 libraries in the table for the loop wich does not use headers. You have to duplicate some process for the 1 outside the loop but once it is built your done.

Greg, I knew this would be the other option and I’m with you, the only problem either putting a “dummy” query or using one library before the loop is, just as you say, you have to change everything twice anytime you make changes, which makes it more difficult especially if you want to leave the automation alone and only want to change the .sql file. Therefore, I wonder if we can propose an enhancement for a later version to possibly set it to allow the first loop’s header row to be created, but none other in the append? I know it sounds difficult, but I thought it would at least be neat to propose. This could save a lot of people a lot of time and create real efficient and neat ways to utilize this loop.

Lastly, I think I also mentioned before an enhancement to have the append to file added to the select to file activity. At this point it only exists in the export wizard, but again the select to file activity is so much more simplistic and would be VERY cool to have this append functionality added to it.

Thanks so much.

Chad

I am with you 100%. If the include header could use the loop count variable to only put the header on if the loop count is < 2 (or < 1 if it starts out as 0). It would make it a lot more elegant. I want to see this append functionality in Select to File also.

Chad, I don’t know if you can access the loop count variable, but you can make your own lopp count variable and use that in a If condition process box to process the 1st run through the loop differently from the rest. Not sure if that will save you anthing over processing the 1st library outside the loop, depends on how you have it coded. It’s the only other thing I could think of that might help.

Yeah, in the end though, even with an if statement looking at the 1st run of the loop, you’d have to have an “original” query for the then condition and then a second query for the else part, meaning you still have to have two of the queries that always have to be updated. Debbie or TDP team, do you think this can be a proposed enhancement to a future version, plus the append feature being added to the select to file activity?

Thanks again.

Chad

I guess I need to ask this question. When would you ever want the headers added when appending? Isn’t the answer, only at the top of the file or if the data being appending is not the same as the above columns? I would need to check with some of the developers but seems we could be more intelligent about this and only include the headers row 1 or if the appending rows have a different number of columns. What do you think?

That’s the exact answer, only at the top of the file. I can’t even think of a situation in which I’d like another looped query to append a different set of columns entirely. That’s exactly right and I’m glad it makes a lot of sense. Its most likely that this loop feature may have not been used in this specific way, generally in place of a UNION with different libraries, which is why this may not have been brought up before, but yes, ideally we’d just love it to have the header information added to row 1, then the additional appends to not include the header row and just keep adding to the data going down the sheet. Again, if this is something that also could be considered in the select to file activity that would also be amazing. I think this could really benefit a company such as ours that has to use so many different libraries and utilize so many UNIONS for queries. Thanks again Debbie!

Chad

Debbie,

I will be looking forward to that change. You da Man! Well,really your not but you know what I mean.

I entered QAT-4224 for Yake to fix.

And yes I wear the pants around here[:)]

Hello, This is a very old thread, but my current problem fits here. I’d like to fill one Excel file with different data depending on the variable, which changes: the ID. So I’d like in the loop create new sheets which name would be created with the variable, like ie. instead of:

  • Sheet1,

  • Sheet2,

  • Sheet3

etc.

I have the following template: “ITEM_#check_id#”, so the final names of the worksheets is:

  • ITEM_1

  • ITEM_2

  • ITEM_3

etc.

Ot maybe it is possible, but I’m missing something. I appreciate for your time and help.

Dear Debbie.

I’d like to ask about one more thing. On the first page of this threat, at the bottom you show the example how to handle the variables in the loop data. It’s clear to understand and implement. In my case i also get the information about the ID (in database it’s the BIGINT). The problem is the error i get in the logs when i run the automation task:

Export_1 - Object reference not set to an instance of an object.

If in the sql query I’ll put the variable in quotes (select … where id = ‘:chid’) then I get the following error:

ERROR [22018][IBM][DB2/AIX64] SQL0420N Invalid character found in character string argument of the function “DECFLOAT”.

I’ll be very happy to know what I’m doing wrong. I keep doing the same thing since 3 days and still no results.

I forgot to mention that the variable get correct value because in the log I can see:

Variable “chid” set to 3. (As expected)

More I read, more I know. I don’t know if it’s relevant, but my current version of Toad at work is:

Toad for DB2 5.5.0.480 with LUW professional.

Mike,

Let me summarize your problem just to make sure I understand you correctly. So, you’re exporting something to an Excel file in the Loop Dataset activity. The export is based on some ID which is BIGINT in the database. Every result set you want to be exported on the separate page of the Excel file and you want those pages to have names with the ID in them like ITEM_1, ITEM_2, ITEM_3, etc. For this purpose you have page name template like “ITEM_#check_id#” where variable #check_id# has to have value of the above mentioned ID in its corresponding loop. While you try to run this script, you’re getting the ‘Object reference not set to an instance of an object’ error. Is it correct?

To better understand what is going on here I need to take a look at you Automation script (tas file) and your SQLs (sql files) unless they are embedded in you Automation script. You can send those files directly to me to igor.manokhin@quest.com

Thanks,

Igor.