Excel Extract from SQL Table Editor

[ankDass]

Hi Team,

Is there any way to extract the SQL Table Editor to an Excel File? 

Currently I have a SQL table editor with multiple pages and the default extract method by right clicking on the column/rows is just extracting one page. Is there a way to extract the full table into an excel file?

Thanks.

[NidhiMangtani]

Hello,

You may have to use GridView component for this, GridView doesn't have limitation on rowcount being exported.

[ChrisLoran]

I would use a TableView to pull the data from the underlying table directly into an embedded spreadsheet, from which you can save as an offline .xlsx file.  The GolfStream application has some examples of TableViews.

GridViews of course (as mentioned above) are another option, however they may take a long time to display if you have a large set of data. You can right-click and export to an Excel XML , which you can load to Excel.
Where possible I would avoid using any methods that convert data to text or CSV as an interim step towards Excel, because that messes up regional differences in number formats, date format assumptions, delimiter characters etc.

[ankDass]

Hey Chris

We have tried extracting the Table using Spreadsheet Rule and TableViews but then we are facing this error when we are refreshing the table view.

any idea on this where how can we increase the memory allocation size of the Table View?

[ChrisLoran]

Before going any further, it would be good to state some data volumes, such as number of rows you are expecting.
Otherwise OS consultants may waste time proposing solutions that may not suit your specific data volumes.

I don't think there is a way of increasing the memory allocation for TableViews. It is going to be highly dependent on the memory on the client PC/laptop, not just on the server.

One option would be to split up the TableView into chunks, and each sheet in the workbook retrieves a separate chunk of the data table. That would reduce the memory demands compared to a single monster-sized data retrieve.

Another option would be to find out what the end-use of this report would be (such as loading the data into a Data Warehouse?), and treat this as a data integration exercise rather than a reporting exercise, and not pass the data through the client PC.
Apologies if I am making suggestions you have already discarded : pls consider we don't have the background context to this exercise.

The last resort would be using a CSV extract, but then of course you get into all sorts of problems with number & date formats, meanings of commas, and expected delimiter characters.

[ckattookaran]

Chris is spot on there with his suggestions, the solutions are always going to be dependent on what is the use of this data. The answer cannot be I need this to analyze. If you are analyzimg, then you really don't need a 100s of thousands of rows of data in EXCEL. There are different ways to handle this and without knowing what it'll used for it is going to be hard to suggest an option. Yes gridview will work. However, you might see that it'll probably take a lonnnnggg time to extract the data or it could even just crash the session.