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

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

Anonymous — Tue, 13 Jan 2015 11:17:10 GMT

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))

));

Thank you very much

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

marcus_sommer — Tue, 13 Jan 2015 11:50:10 GMT

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

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

datanibbler — Tue, 13 Jan 2015 11:55:16 GMT

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.

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

Anonymous — Tue, 13 Jan 2015 11:59:34 GMT

No i have 120 lines and 800 columns

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

rajeshvaswani77 — Tue, 13 Jan 2015 12:08:03 GMT

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

marcus_sommer — Tue, 13 Jan 2015 12:08:08 GMT

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

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

rajeshvaswani77 — Tue, 13 Jan 2015 12:11:39 GMT

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

regards,

Rajesh Vaswani

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

Anonymous — Tue, 13 Jan 2015 13:05:34 GMT

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

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

peter_turner — Tue, 13 Jan 2015 13:15:27 GMT

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?

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

rajeshvaswani77 — Tue, 13 Jan 2015 13:28:01 GMT

Cant you please use a full new excel.

thanks,

Rajesh Vaswani

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

marcus_sommer — Tue, 13 Jan 2015 13:38:22 GMT

Before going further check the hint with concatenation from peter turner

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

rajeshvaswani77 — Tue, 13 Jan 2015 14:20:06 GMT

Have an exit script statement just after the load from excel

thanks,

Rajesh Vaswani

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

Anonymous — Tue, 13 Jan 2015 14:26:23 GMT

I created a new sheet, copied the data, and deleted the old sheet. It works fine now. Mighty Excel!

Thanks to all for your suggestions.