Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philippon
New Contributor III

Fetching issue - Excel - Too many lines loaded - Takes time

Hello

I am fetching data from an Excel workbook. Each Excel sheet is laoded into a Qlikview table.

All goes smoothly except for one sheet where 13,000,000 lines are fetched even though I only have 80,000 data point. The structure of the sheet is very similar from the other sheets in the same workbook, and in the other cases there is no problem.

Any idea/suggestion?

The cript loading the data is:

Table:

CrossTable(ID, DATA, 1)

LOAD *

     FROM

[file.xlsx]

(ooxml, embedded labels, table is DATA, filters(

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 2))

));

Thank you very much

1 Solution

Accepted Solutions
rajeshvaswani77
Valued Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

As a solution, create the new excel and put in the same data and it should work.

regards,

Rajesh Vaswani

12 Replies

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Not really sure what you meant but it's a crosstable-load and if you have 80000 rows and 162 data-columns you will get about 13000000 rows.

- Marcus

datanibbler
Esteemed Contributor

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Hi,

what you could try, if it's possible in your scenario, is load that table beforehand and store it into a qvd. That will not make it faster, but shift the workload, so your actual app will be loaded faster.

philippon
New Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

No i have 120 lines and 800 columns

rajeshvaswani77
Valued Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Hi Pierre,

Have faced this today.

Its thinking that there is data in the cells below also.

Could be due to some blank cells.

Mostly issue with excel.

Thanks,

Rajesh Vaswani

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Are you sure that it be only 120 rows and no more rows with any data in it - which could be invisible maybe only spaces. Further is this load-statement part from any loops?

- Marcus

rajeshvaswani77
Valued Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

As a solution, create the new excel and put in the same data and it should work.

regards,

Rajesh Vaswani

philippon
New Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Yes I am sure, I deleted all other cells to make sure they are empty.

peter_turner
Valued Contributor

Re: Fetching issue - Excel - Too many lines loaded - Takes time

It may also be worth looking if your table is being concatenated to another table already loaded in QV?

Also you might be able to load the excel file as a normal load, then apply the cross table function as a resident load once the data has been loaded in QV?

rajeshvaswani77
Valued Contributor III

Re: Fetching issue - Excel - Too many lines loaded - Takes time

Cant you please use a full new excel.

thanks,

Rajesh Vaswani

Community Browser