Toad World® Forums

Exporting 8.1MM row Dataset


#1

I’m having trouble exporting an 8.1 million row dataset. Tried going to Excel, it says that it is successful, but the resulting file is 4 KB…so that’s no good. Tried going to tab delimited, but it errors right-away and stops.

Toad Data Point 4.3.0.718 (32 bit)

Log from tab delimited attempt…

Saved the query with results, closed Toad and restarted…same result.

Now trying sending to Local Storage and exporting from there…maybe that will work.


#2

Recent versions of Excel are limited to around 1 million rows max. As for the tab delimited, not sure, wondering if it has anything to do with 32 bit vs 64 bit. Curious if local storage worked.


#3

The local storage method worked.


#4

You can use the Excel data model (Power Query and PowerPivot) to get more than 1 million rows of data in Excel. You just copy your SQL from Toad and input it into the dialog box when getting data (from the Data tab in Excel). I use this a lot and have used data models with >10M rows


#5

I re-read Mr Barkers reply and this is the same answer tried to delete it, won’t let me.

Adding to Mr Barkers reply, depending on the data source, I use TOAD to develop my code and if needed run the query within Excel Power Pivot to get the data, eliminates the need to export and re-import. It allows for easy refreshing of data.


#6

I have a similar issue. I just wanted to know if its possible to set up a do while loop in toad automation.

something along the lines of: export data 1 million rows per csv file until no rows are left to export.

so sort of renaming function: each 1 million row csv file will maintain the set file name but maintain uniquness with 1,2,3 etc per file.


#7

There is a while loop activity as shown in this video https://www.youtube.com/watch?v=ygrobaF3_Iw