Hello All,
I have started using Toad Data Point. Earlier I was using Toad for Oracle.
I would like to know how to extract the complete results set from the query to excel. I tried using the Quick Export option and also the Export option, however it is only exporting partial data. For example I have a query result of 150,000 rows. I am able to export some where between 1,000 to 20,000 rows and nothing beyond that.
In Toad for Oracle, I just click export and I can export the complete result set to excel file.
Make sure you are exporting to an xlsx file and not an xls file (old excel row limits are around 30000 rows, xlsx can go to 1.2 Million rows if you have enough memmory). On the memmory part, if you run the query and display the results, it is using your PC’s memmory to store that result. Then when you export to Excel you may not have enough memmory left for Excel and both copies of the result at the same time. Instead try running your query directly to the Excel file (Export Wizzard or Select to File in automation). If that does not work export to csv file (uses a lot less) and then open the csv file with Excel.
Usually what is happening is that the Editor has a paused query. Look to the right hand corner, does it say "Record 1 of 1000 scroll for more). If so that means it is paused. When you right click it should prompt you and ask if you want to read all of the rows so you have a complete result set. You would need to say ‘yes’ otherwise you will get only a partial set of data. This sounds like what is happening.
Note: When connected to Oracle, F9 retrieves the first 1000 rows and pauses. F5 retrieves all rows.