Number format not exported to Microsoft Excel ARTICLE NUMBER: 000005592 | LAST MODIFIED: 2020/05/04
Description
Chart object is exported from QlikView to Microsoft Excel. The number formats in Excel looks different than in QlikView.
Cause
For exports of straight or pivot tables the number of lines might be higher than the XLS file format limit, leading to export in CSV format as described in related article
Export to Microsoft Excel's XLS file limited to 65536 rows and 256 columns. CSV files are pure text based files, without formatting details. This means that Microsoft Excel must apply a reasonable format based on it's own algorithms and logic, which can lead to a different visual format than expected or exported from QlikView.
If the field value in QlikView is formatted as Mixed, the export to Microsoft Excel will be done as text. Exporting value as text means that Microsoft Excel will decide on a new format or keep text as format.
Resolution
The number format can be affected by applying a desired format in Microsoft Excel after export.
Common solution to improve export from QlikView is done in two steps.
- Ensure that exported table has less rows than the XLS file format limit. Object's Calculation Condition can be used to enforce user to make a limiting selection.
- Apply a uniform and defined numerical format to the chart expression. For example if the data is decimal numbers, the format should be decimal number and not Mixed. In this context consider if the format can be applied already during load, to make application setup as simple as possible.
