topic Export Excel in QlikView

Export Excel

Mon, 03 Feb 2014 08:01:51 GMT

Hi Experts,

I have an requirement to export to excel the larger data like 1 Million records from the Qlikview through the Accesspoint. As we have the default export excel functionality upto 65K records it exports to excel after that it converts into CSV.

But my requirement is, it should be export excel. Because this data client will import into CRM System,Is there any possibility.

I have tried with the changing of RowLimitForCsvInsteadOfXls=10000000 cap limit in the settings of Qlikview. but i coulsn't succeed.

Regards,

Santhosh G

Re: Export Excel

puttemans — Mon, 03 Feb 2014 08:07:29 GMT

The problem is not Qlikview, but Excel. Excel cannot hold this huge number of records in one single file.(I experience the same issue). A solution we use is to chunk the big file into several smaller ones that Excel will allow.

Re: Export Excel

Mon, 03 Feb 2014 08:11:03 GMT

Hi Johan,

Please explain me clearly or send me the sample which you are doing.

Thanks in Advance.

Regards,

Santhosh G

Re: Export Excel

puttemans — Mon, 03 Feb 2014 08:37:26 GMT

Hello Santhosh,

If your client can upload multiple excel files, then you could first assign a unique number to each record through 'Recno = AutoNumber()'. Then through a loop, you fetch a certain number of records (allowed by Excel). We did this as followed (see below).

This allowed us to treat 128 (2 power7) at a time. (you may choose much bigger intervals) Within each iteration, you can then save the file in Excel, adding the $(i) in your saving instruction. That will save you a number of files equal to your number of iterations.

SET i=Null();

SET j=Null();

SET k=Null();

SET l=Null();
LET k = 7;

LET pwr2x=Pow(2,$(k));
SET k=Null();
FOR i = 1 to XXX (depending on the total number of records you have and your selection width)

LET j = $(i)*$(pwr2x);

LET k= If($(i)=1,0,$(j)-$(pwr2x)) ;

$(i):
LOAD
*,
FROM
File (qvd)
WHERE Recno > $(k) and Recno <= $(j);

STORE $(i) into Excel$(i).xls;

NEXT;

Re: Export Excel

Mon, 03 Feb 2014 08:55:12 GMT

Thanks for reply. But my requirement is not through the Qlikview developer. On web how can you export when you click on the export button.

Re: Export Excel

ThornOfCrowns — Mon, 03 Feb 2014 09:12:47 GMT

I thought that the XSLX format could take 1,048,576 rows by 16,384 columns?

Re: Export Excel

Mon, 03 Feb 2014 09:26:36 GMT

Hi James,

You are correct. But the qlikview have the cap limit of 65500 records, after that it's exporting as a CSV. if you are having the XLSX(Microsoft office 2007 later version) also.

Re: Export Excel

puttemans — Mon, 03 Feb 2014 09:36:18 GMT

Indeed, as from the 2007version apparently.

+-----------------+-----------+--------------+---------------------+

+-----------------+-----------+--------------+---------------------+

| Excel 2010 | 1,048,576 | 16,384 | XFD |

| Excel 2007 | 1,048,576 | 16,384 | XFD |

| Excel 2003 | 65,536 | 256 | IV |

| Excel 2002 (XP) | 65,536 | 256 | IV |

| Excel 2000 | 65,536 | 256 | IV |

| Excel 97 | 65,536 | 256 | IV |

| Excel 95 | 16,384 | 256 | IV |

| Excel 5 | 16,384 | 256 | IV |

+-----------------+-----------+--------------+---------------------+

I wouldn't know however how to change setting in the web export Santhosh

Re: Export Excel

sudeepkm — Mon, 03 Feb 2014 09:42:24 GMT

Please see if the Action > Export can help you in this case.

CSV format is one of the export formats in that format more records can be adjusted unlike xls (97 - 2003 format).

But I've not tested it yet in the Access Point. Please let me know if it works.