Data Export Problem

customaware

Am using Aware IM V8.8

Here is the issue....

I have a BO that currently has about 70,000 records in it across 4 years worth of data.

When the data is displayed to the User, they will typically want to filter that data down to a single month.
The data for 1 Month is between 3,000 and 4,000 rows.

We then want them to be able to export that filtered data set to either csv or Excel via the export options
in the Panel Toolbox.

Problem is .... Excel.... Only exports the records displayed in the current page. I can increase the page size but anything more than 1,000 becomes
problematic. If I increase to say, 5,000 (which would get all of the records for that month) the export crashes

Or, with csv.... the export is not limited to the filtered data set... It actually exports 65,000 records.

Does anyone have any suggestions resolve this other than resorting to building a Stored Procedure?

BLOMASKY

I am pretty sure Jaymer has a solution to export. Reach out to him.

Bruce

Jaymer

Bruce is referring to the work I have done on the PDF export, which is almost made me go crazy.
I’ve put multiple hours into it.
But it’s finicky.
And it really needs a little assistance from Vlad to make it work better.
so this will not be my first choice to do an export.

Jaymer

The strategy I would take is this:

Add input fields to the grid toolbar which allows the input of a couple of the major feels of criteria to export a set of records. Don’t allow column filtering.
Fields Like starting date and ending date (or 2024Jan), and for example, let’s say region. Or product category.
Those are fields stored in the reg user table, or session variables.
And they change one and it instantly reruns the query and user Can verify that the set of records they’re seeing looks like the right data and quantities that they want to export.

Then you have a button up there that says export, which runs a process.
The process does a find using that exact same query name, which now results in the exact same records that were just showing on the screen.
Then just do an export using an export template and the files gonna get downloaded and there’s your month of data, regardless of how many recs.

I stopped using xls imports because Excel so nicely imports a CSV.
If you have commas in your data, you may need to switch to a vertical pipe. I can’t recall if in this method aware will make a “proper“ CSV with quoted strings.

customaware

Thanx guys.

Resorted to a mixture of Stored Procedure and Python script....

Turned out to be a much better solution and faster.

ACDC

I have also been wrestling with the Export to Excel. In the end I resorted to copy and paste direct from the Grid into Excel.
It works, Kendo supports this and it keeps the correct formatting. Its not an elegant solution, but it is a work-around

customaware

ACDC wrote
I have also been wrestling with the Export to Excel. In the end I resorted to copy and paste direct from the Grid into Excel.
It works, Kendo supports this and it keeps the correct formatting. Its not an elegant solution, but it is a work-around

DM me and happy to share. Haven't got time to build a demo but happy to point in the right direction for you to get the SP / Python
solution working.

PointsWell

No technical solutions but the Jakarta-poi Jar is 1.5.1 and was shipped in 2002.

In all likelihood there are probably a number of improvements between then and now that could be leveraged.

hpl123

I would have looked at the Kendo UI excel export options first and foremost as things like this can be configured. Take a look here for example: https://docs.telerik.com/kendo-ui/api/javascript/ui/grid/configuration/excel.allpages .

A sidenote (not entirely related to the question but still) and possibly an interesting option is, if you look at the Kendo UI example on the page above you see a HTML widget which loads a JSON resource into a Kendo UI grid and as we have the Kendo UI library in Aware, I would think it possible to use the HTML code in the example and prepare an external JSON URL like the example shown (https://demos.telerik.com/kendo-ui/service/products) to display the data in a grid. This can most likely also be used to "solve" the initial question, albeit in a roundabout way, as you can change the code in the example and then have the excel export export all pages etc. etc..

nhofkes

customaware wrote
Thanx guys.

Resorted to a mixture of Stored Procedure and Python script....

Turned out to be a much better solution and faster.

Mark, can you elaborate on the Python script? On which machine is it running (server or user computer) and what is it doing?

ACDC

DM me and happy to share. Haven't got time to build a demo but happy to point in the right direction for you to get the SP / Python
solution working

Thanks, I sent you an email

In the meantime, I have been able to create a solution using Python to create an Excel file from a .csv file which works really well. My Grid is a fairly complex grid with grouping and have been able to duplicate the same layout using Python
The Process:

Export grid to .csv on the server
run a Python script to convert the csv file into an Excel file to the required layout
Import the Excel file back into AwareIM and present to user.

This does seem to be an ideal solution providing better formatting options than Kendo Export to Excel feature