In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

[c_rusu85]

Hi,

In QSB when I download a sheet in xlsx all the numbers have in front a ' sign (see attached) that makes the numbers to be interpreted in excel as string not as numbers.

In QSB I setup correctly in number formatting the field to be number. In the old version of Qlik Sense Cloud this problem didn't exist, for the same Qlik sheet the same data in the downloaded xlsx file was presented as numbers without the ' sign in front.

Is there any setup I must make in QSB or any way I can correct this in QSB without manually editing the xlsx file each time I download it by using find and replace?

Thanks.

[marcus_sommer]

You could try to load your field with: purgechar(Field, chr39()) as Field

- Marcus

[c_rusu85]

I tried in the expression with purgechar(ExpressionCode, chr(39)) but when I download the sheet from QSB to a local xlsx file the problem (the single quote apostrophe in front of each number) remains.

In the QSB sheet the data is seen correct, as number!

[marcus_sommer]

You don't want to load the data into Qlik else to export them from there to Excel, right? Then I suggest to set the number-format for this field to numeric and/or to format this column in your export table to numeric or maybe also using num() within the expression.

- Marcus

[c_rusu85]

In Qlik there is no problem, the data is seen as number, the fields are measure and I use expression code in order to process data "live" after is loaded.

The problem is when I download the sheet from Qlik to xlsx. This problem didn't exist in the old Qlik Sense Cloud, when I downloaded the data in xlsx file all fields that was set as number in Qlik in the downloaded xlsx file appeared as number without the single quote apostrophe in front of each number.

The same app (i tried also with a new created up) in the new Qlik Sense Business have this problem.

My opinion is that its not from my code or from my Excel, its from the way that the new QSB generate the xlsx file by automatically inserting a single quote apostrophe in front of each number.

PS: I opened a case about this with Qlik support and I got a message from no-reply@qlik.com to send them my app so I`m not sure if they got my app and the case link I received in the email redirects to the Qlik index page 🙂

[marcus_sommer]

I think Qlik don't use any own logic for the Excel exports else it takes any available *.dll's to perform the job. Unfortunately there are several different interpretations from the ooxml-standard: https://en.wikipedia.org/wiki/Office_Open_XML and maybe they replaced one with another or just the release of it might have changed. Even MS used different ones and so is an Excel-file different if it's created with the desktop-client or with the online-client.

If there are no further export-settings within the QSB available which may have an impact on the results you could look within the various xlsx-parts (changing the extension from xlsx to zip and then opening it within the windows explorer and you will find various xml-files). Especially if you compares it with a "normal" xlsx you should find differences and with them maybe a hint how to bypass the issue. Of course it will need some time and there is no guarantee that you get a working solution ...

Further a search within google to a similar behaviour by other tools may give some insights.

- Marcus

[jsteyn]

This used to work in the old QSCB platform and for some time in its infancy of the new QSB platform.

Myself and numerous people have raised this on multiple threads on this forum, would be nice if we can get an update on this from Qlik here?