Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

[XLS LOAD] limit number of lines being loaded

good morning,

i have a LARGE excel file, with many sheets. usually, there is only one table per sheet.

but in this case, there are sheets with like 7 diferent tables (with different types of data)

qlikview allows to ignore a number of lines before it starts to load (header).

BUT, is there a way to STOP the load after a certain amount of lines ?

because, if it continues to load, it creates data-garbage after a few lines

(f.e. reading names, after a few lines there are numbers in the same column = mixed numbers and names)

any suggestions ?

thanks

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

[XLS LOAD] limit number of lines being loaded

Hi,

You can use a where clause:

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7,

     @8,

     @9

FROM

[Test.xls]

(biff, no labels, table is sheet1$)

where RowNo()<1000;

You can replace the 1000 by the number of lines you want to load.

Regards,

Erich

4 Replies
erich_shiino
Honored Contributor

[XLS LOAD] limit number of lines being loaded

Hi,

You can use a where clause:

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7,

     @8,

     @9

FROM

[Test.xls]

(biff, no labels, table is sheet1$)

where RowNo()<1000;

You can replace the 1000 by the number of lines you want to load.

Regards,

Erich

Not applicable

[XLS LOAD] limit number of lines being loaded

where RowNo()<100; works

when there are f.e. 10 excel files, it reads all the necessary data from file1 OK,

BUT QV reads only the first line of data from the following files.

erich_shiino
Honored Contributor

[XLS LOAD] limit number of lines being loaded

Hi, Alexander.

Please, try using recno() instead of rowno(), this way you can control the load on each input table.

Regards,

Erich


Not applicable

[XLS LOAD] limit number of lines being loaded

You are right, this was allready solved in:

http://community.qlik.com/message/162804#162804