Can't Control Format When Exporting Query to File

kklosson

I have a query, and it includes some date attributes. All dates are formatted MM/dd/yyyy. I have a process that exports the query to a .csv file. Example:

EXPORT ‘QueryName’ TO ‘FileName.csv’

In this mode, it seems the display parameters of the query are not respected. Rather than using the labels provided in the query, it displays the attribute names. While the date format in the query (and the associated attribute) is MM/dd/yyyy, it displays dd/MM/yyyy for shortcuts of the date data type. So, it seems that when exporting a query to a file, the query is just dumped from the database rather than using any display features provided in the query, and shortcuts are handled a bit differently and seem to default to a certain date format. Perhaps that can be improved on the platform.

Expanding: Ultimately, I need the contents of a table (via query) dumped to Excel. Every method I know of requires exporting to CSV. When doing this, any shortcut to a date attribute loses formatting and goes to dd/MM/yyyy format regardless of the query format. This includes using the Export to CSV header operation.

Is there a way?

nhofkes

Yes, there is a way.
There is a standard Export function that you can specify as a Panel Operation (type: Export XLSX).

For some reason (can’t quite remember) I found the output of that operation not ideal. I now use a JS render script that replaces this operation by the standard Kendo UI function saveAsExcel(). Furthermore, in the same render script I make some modifications to the exported Excel file:

Change filename to that of the table
Change worksheet name
Remove columns that are hidden in the grid
Set the column width
Set format of a number column to the same format as on screen
Remove the bottom row with the totals and replace it with my own calculated column totals, in the correct number format

I have not yet implemented date formatting in this render script as there was no need for it, but that should be doable in the same way as I am formatting the numbers.

Note that because this uses the standard Kendo UI Grid export functionality, it is not suitable for exporting large datasets as it is done in the browser instead of the server. But for my purposes it works great.