8 Replies Latest reply: Feb 3, 2014 4:42 AM by Sudeep Mahapatra RSS

    Export Excel

      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 Johan

          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

              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 Johan

                  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
                  James Summerson

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

                    • Re: Export Excel

                      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 Johan

                        Indeed, as from the 2007version apparently.

                         

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

                        |                 | Max. Rows | Max. Columns | Max. Cols by letter |

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

                        | 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
                      Sudeep Mahapatra

                      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.

                      exporttocsv.png