topic Re: Export to Excel issue in QlikView

Export to Excel issue

Mon, 30 Dec 2013 12:30:31 GMT

Hi,

We are facing an issue while exporting data to excel. The problem occurs when the "export to excel" of a table exports into CSV format.

We have a primary key which is a set of numbers. When this column is exported to CSV, some keys are preceded with some special characters. I checked if some particular keys have this issue. But that isn't the case. Each export results in completely different keys having special characters added before them.

For example, If the key is "7891234" then when exported to CSV we get "$^!*7891234".

This issue comes up only when it is exported to CSV, that is when rows are more than 65000. When rows are less than 65000, the exported file format is XLS and this issue does not occur.

I tried explicitly converting the key to number format and also to text format. Neither of these solved the problem.

Has anybody faced this issue? If so, then how did you solve it.

Thanks & Regards,

Chait

Re: Export to Excel issue

Gysbert_Wassenaar — Mon, 30 Dec 2013 12:38:58 GMT

There's a setting that you can change to increase the limit before the data is exporting in csv format:RowLimitForCsvInsteadOfXls

On the server you can change the limit in the QVS settings.ini file

[Settings 7]

RowLimitForCsvInsteadOfXls=65500

In Qlikview Desktop you can change this with an 'easter egg' setting. In the Help menu select About Qlikview. Then right click on the Qlikview icon in the lower left corner. Find the entry RowLimitForCsvInsteadOfXls. You can then increase the value from 65500 to a higher number. Note, this may cause a performance penalty on your server.

Re: Export to Excel issue

Anonymous — Mon, 30 Dec 2013 12:39:18 GMT

Chait

QlikView exports to Excel using the BIFF which has limit of 65,000 rows.

Best Regards, Bill

Re: Export to Excel issue

Thu, 02 Jan 2014 09:23:49 GMT

Gysbert Wassenaar

Thanks for your reply. I understand that this is a work around for the issue I am facing. I would need to request the server admin to make this change in the settings.ini file I guess.

But I was trying to figure out why I am facing this issue. Why are these special characters getting added to the data in the first place.

Is this some kind of a known bug? Has anybody else faced a similar issue?

Regards,

Chait

Re: Export to Excel issue

Siva_Sankar — Thu, 02 Jan 2014 09:35:21 GMT

You can try exporting row 1 to 65,000 should be exported to 1st sheet in excel or csv and

row 65,001 to 130,000 should be exported to 2nd sheet in excel or csv. You can do this using the macro.

refer the following link,

http://community.qlik.com/message/383894#383894

Regards.

Siva

Re: Export to Excel issue

Thu, 30 Jan 2014 07:18:37 GMT

Hi Gysbert,

I am also having the similar requirement. End user need to export the large data (1 Million) from access point.

I have tried the both the options which you have given on the previous reply. Its not working for me on the access point.

Is there any other workarround.

Regards,

santhosh

Re: Export to Excel issue

Anonymous — Fri, 26 Sep 2014 06:12:00 GMT

Hi Gysbert Wassenaar

I have a similar problem with one of my client. They are trying to export more than 500000 rows but the export format converting in csv.

I have changed the RowLimitForCsvInsteadOfXls limit to 9000000 in both application and server's setting.ini but the issue is still there.

Is there any way I can help them to resolve this issue. I don't want to recommend macro as there are more than 20 straight tables for export.

Thanks

Satya

Re: Export to Excel issue

Anonymous — Wed, 21 Jan 2015 03:54:13 GMT

Qlik should support the xlsx format instead of xls file format

I am having problem with date format because csv file does not have info on date format so Excel can't tell if a number is dd-mm-yyyy or mm-dd-yyyy format. The workaround for us is to format date values using dd-mmm-yyyy format instead so that Excel can recognize the date format.

Re: Export to Excel issue

paulodantas — Thu, 23 Aug 2018 16:06:59 GMT

Hello Satyadev,

I think this is a problem from your Office. What is the version?

For more recent, maybe you could export and this do not convert to csv file.

Best regards.