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

[Anonymous]

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

[marcus_sommer]

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]

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.

[Anonymous]

No i have 120 lines and 800 columns

[rajeshvaswani77]

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

[marcus_sommer]

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 · Accepted solution]

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

regards,

Rajesh Vaswani

[Anonymous]

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

[peter_turner]

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]

Cant you please use a full new excel.

thanks,

Rajesh Vaswani