How to easily export all records

If you think that something doesn't work in Aware IM post your message here
Post Reply
jannes
Posts: 100
Joined: Tue Jul 02, 2019 12:22 pm

How to easily export all records

Post by jannes »

How to easily (userfriendly) export ALL records / fields from a BO to EXCEL ?

1) Via grid there is a limit of 50 records.
The user can select "All" but then only 20 records show up

("All" does not show up when expected. See other issue).

2) So we have to select (Grid, other settings) "Fetch records for all pages".
In this case :
- the user must select "All"
- the query is taking a lot of time. The user must wait at least 30 a 60 seconds.
- The user must select the XLSX export - button (with process EXPORT EXCEL).
So, this is not userfriendly.

3) Using a process "EXPORT DOCUMENT ExportPersoon TO FOLDER 'c:/mydocuments' AS XLS" will generate separate XLS's for each record.
Besides this, the format is not XLSX.

4) Using the workflow will export to CSV. We would like to have XLSX.

5) Using a database-view is possible but doesn't speed up

I wonder in what way other developers deal with this issue..
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: How to easily export all records

Post by Jaymer »

--> "I wonder in what way other developers deal with this issue.."

I export CSV
Excel imports that perfectly.
Even by just double-clicking the file, if you have excel installed locally

Here's my Inventory Grid:
InventoryGrid.png
InventoryGrid.png (197.19 KiB) Viewed 4812 times
Currently filtered to 474 records (so it wasnt so large).
Showing only 25 per page (quick, no slowness, a normal grid like any other)
The Panel Operation to ExportCSV (the built-in function) sends all records (VERY quickly) to a csv and automatically downloads in Chrome to my downloads dir.
User can just click on the file at the bottom of the screen to open.

Can't get much easier than that.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
nhofkes
Posts: 94
Joined: Mon Sep 07, 2020 6:03 am
Location: Netherlands

Re: How to easily export all records

Post by nhofkes »

I believe that it depends on the locale settings on the client computer whether Excel correctly imports the CSV file. Unsurprisingly, the CSV file uses a comma as delimiter. This works fine if the OS Regional Settings on the client computer also has a comma as list separator (which Excel then uses), for example in the US regional settings. But in my case, where the regional settings are set to Dutch (Netherlands), the default list separator is a semicolon ';' instead of a comma.
List Separator.png
List Separator.png (24.26 KiB) Viewed 4475 times
As a result, Excel does not open CSV files correctly - because fields are not recognized, everything is pressed into a single column. I can of course switch the regional settings to e.g. US, but that applies to all applications on my computer, not just Excel - let alone limited to the way that Excel handles CSV files.
Perhaps this is why Jannes would prefer an export to an XLSX file instead of CSV. I tend to agree with that, as the XLSX would open correctly in Excel regardless of the computer's regional settings.
Niels
(V9.0 build 3241 - MariaDB - Windows)
Post Reply