Search Kit: data exported to spreadsheet as strings where numbers expected
Overview
When exporting spreadsheets from Search Kit in some cases data types seem to be exported incorrectly as strings where they should be numbers.
Reproduction steps
- Create Search Kit search for Contributions on demo.
- Add columns for Total Amount and Date Received and click Search.
- Export results via Action → Download Spreadsheet and choose .ods (or .xlsx).
- Open document with office software.
Current behaviour
While the Contribution ID column is correctly formatted as a number, Total Amount and Date Received are formatted as strings.
Note: Numbers are also left-aligned in Search Kit exports (column A).
Here is what the file looks like in LibreOffice 6.4.7.2:
…and here the corresponding XML in content.xml:
Expected behaviour
Numbers and Date fields are exported in correct data type.
Additional ideas / Comments
- it would probably be very useful to have more Field Transformations to have some choice for data types, esp. to be able to output money values with our without money signs
- maybe numbers should not be left-aligned anymore (as it is the standard behaviour in spreadsheets)
Environment information
- CiviCRM: 5.52.alpha1 and 5.50.3
- PHP: 7.4
- CMS: WordPress 6.0 and Drupal 9.3.14