Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Specialist III
Specialist III

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

regards,

Rajesh Vaswani

View solution in original post

12 Replies
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
Champion
Champion

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
Not applicable
Author

No i have 120 lines and 800 columns

rajeshvaswani77
Specialist III
Specialist III

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
Specialist III
Specialist III

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

regards,

Rajesh Vaswani

Anonymous
Not applicable
Author

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

peter_turner
Partner - Specialist
Partner - Specialist

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
Specialist III
Specialist III

Cant you please use a full new excel.

thanks,

Rajesh Vaswani