Wrong value format when copying values to Excel

[#1]

Hi all,

Can someone help me with copying values to Excel? I have customer who exports values to Excel but value format is incorrect in Excel. For example if value is 17,5, Excel interprets value as date and shows value as May 17th. QV application has decimal separator comma as well as Excel does. I can not repeat this problem, if I export values to Excel, all values are shown correct. Both customer and I have Office2013, only difference is that my Office is in English and customers is in Finnish. We have checked Excel options that desimal separator is comma. What else should I check and change that all values will be correct? I have tested in QV to change number format to number or integer but it didn´t help. That is weird because if I export values to Excel from customers application, everything is fine..

Best,

Suvi

[Siva_Sankar]

Go to Help Menu->select About Qlikview->right Click on Qlikview Symbol(Which you found in left Bottom corner) ->Then Setting Screen Will appear->select ExcelExportMixedAsText->change it to 0

[jonathandienst]

I think that your problem is in Excel, not QV. Excel tries to be "helpful" when loading the text output from QV. I suspect that the problem lies with the date format set on your client's computer. This is being confused with the number format.

[#4]

Hi,

After exporting to excel, select that column and change the date format to number format through format cell option.

I do not think we can do anything for this from qlik side.

Thanks

[ToniKautto]

How much data is the your customer exporting?

If you reach above 65500 rows, or so, the export will be an CSV file and then Excel will interpret the format as there is no meta data in the CSV file.

Typically you can see the file extension int he Excel window caption bar.

[ToniKautto]

Of course we can, but first we need to understand the reason for the issue

[#7]

IN excel format the row as a Text before it is copied in then use a formula to convert to a number =value(cell ref) should do it.

[#8]

Hi,

Thank you for your quick responds!

The issue is exporting price lists to Excel (and forward to printer). The number of rows and columns varies (chart type is Pivot with price list as horizontal axis and item details as vertical axis), that is why I don´t prefer option where client has to edit column values in Excel.

And converting column to number did not help either, Excel interprets column as General and for some reason, uses point as decimal separator, even though Excels default is comma. I cannot repeat this because when I copy values to Excel (from customers application), decimal separator is always comma, not point.

Siva, I have actually tried your mentioned method but with no success.

[ToniKautto]

In addition to 65500 rows there is also a limit of 256 columns in the XLS file format. So when you cross either of these limits the export format will be CSV. Do you think this is what happens in your customer environment?

When the CSV is exported instead of XLS QlikView will have to choose if the exported value is the visual value or the underlying numerical value. This could explain why the export would get 17.5 instead of the visual value 17.5.

[shannonmarshall]

Toni,

I have tried the change Easter Egg setting but I still do not get the results I am looking for.  We have our values formatted as Number with 2 decimal places and use the format pattern ###,##0.00;(###,##0.00);-.  How can I keep this formatting when a user "Sends to Excel"?

Thanks