Problem when exporting to Excel

[#1]

Hello, I have a medium size Qlikview application and we're facing a strange problem.

When we export data to excel, the values of integer columns sometimes are exported as text and not as numbers.

Above there is an example of what I'm saying. Green ones are how i expect Qlikview to export integer columns and Red ones are the unexpected behavior ( text transformation ).

One thing i noted is that Qlikview seems to have a max number of rows and if you pass this limit, then everything turns into text. I made some tests and in some point between 64k and 65k the text behavior starts to appear.

1.539
1.556
1.126
1.591
1 237
1 031
2 003
1 300
2 467

Do you guys now if this is a characteristic of the product or perhaps If there is something i could do to prevent that behavior ? Don't know...

Thanks in advance.

[hic]

The numbers should be formatted in a way that Excel can recognize, usually without thousand separator.

Further, the Excel BIFF format has a limit of 65k. If you want to export more lines, you should use csv.

HIC

[jaimeaguilar]

Hi,

I'm not sure about the format of your numbers, it's weird that green ones export in the correct format and red ones not, maybe if you could upload a sample of your qvw, I could help you with that. About the issue of the rows, it is not a QlikView Issue, but an Excel issue. Prior to Office 2010, the maximum number of rows supported for a XLS file was about 65,000 rows. However, after Office 2010, the number increased up to 1 million rows. However, you also have to keep in mind that other factors could affect, like bandwidth, number of columns, and well in general you may try to reduce the amount of data you export to excel, making some filters (for example, month, year, product categories, etc),

regards

[ashfaq_haseeb]

Hi,

Right click on chart and click export and save file in .csv format.

Regards

ASHFAQ

[#5]

Thanks for the answers.

Just clarifying some things. I know excel has this row number limitation, thats not the problem. The 64~65k limit I mentioned is the limit that influences the data export behavior ( The number of rows continue beyond the 65k limit, Office 2010, to more or less 300k, which is the full expected result. What differs from the exportation with less and the one with more than 65k is the format of the number column given as a result, somewhere between 64k and 65k or less means Green Ones, more than that limitation results in Red Ones ).

Somewhere between 64 and 65k the integer column starts being exported as text, not as as number. I don't have both behaviors on a single data exportation. Depending on the number of rows I get one or another, not both.

[patricio]

This is the answer I had from QlikView when I had the same problem:

"According to QlikView Research and Design, if an object is exported to Excel that contains more than 65000 cells, because of Excel's limitation on number of individually formatted cells, the object will be exported as a .csv file. Any formatting (including decimal places), will not be conveyed"

I noticed at that time that the number was not exactly 65K but was a number close to 65K.

To avoid loosing too much decimal positions I increased the number of decimal position in chart properties/Number. Having 4 decimal positions defined, then the export was keeping those 4 positions even if exporting more than 65K. If the positions were not defined under the chart properties/Number then all the decimal positions were lost when exporting more than 65K.