Toad World® Forums

Excel connection to Oracle Table

We have created a view in Toad which contains approx. 1.6 million rows

I have successfully connected to the Oracle View Table, created a Power Query View / Power Pivot in Excel however it takes forever for the data to load and it keeps on bombing out. Does anyone have any suggestions in dealing with large volumes of data…our goal was to have the end users connect to the view using excel and then filter using pivot tables.

Thanks in advance

Paul

The fastest way to export this many rows to Excel is probably to export to tab or comma delimited, then import that from Excel.

If that’s not an option, try using the “Excel Instance” export option rather than “Excel File”.

We had problems with 12.7 and prior bombing out sending large volumes of data to Excel. This will be fixed in 12.8, which comes out soon (sometime next month).

Thanks John for your reply. I tried using the Excel Instance and it errored out due to memory restrictions. I also tried the comma delimited and this seemed to have worked however after analyzing the data in the columns got messed up since we have commas in some of the data…is there a way around this and delimit only the column headings? Thanks

To avoid “out of memory” errors, be sure that “Display all results in grid” UNchecked. If that was checked before, then uncheck it and try again with Excel Instance.

As for delimited, you can use TAB or Semicolons or whatever you want as the delimiter. If you prefer to not have delimiters at all and just have the fields start at specific positions in the file, you could try “Fixed field spacing”. Excel can import that too.