topic Re: Load X Number of Lines From Excel File in QlikView

Load X Number of Lines From Excel File

Thu, 11 Dec 2014 18:45:19 GMT

Is there a way to specify how many lines to load from an Excel file? I have a very large Excel file that somehow got ~900K blank rows (with ~450 columns) that are treated as data. This causes Excel/QlikView to crash when I try to work with the data. I would like to be able to load say only the first 2,000 lines of the file into QlikView if possible.

Thanks.

Re: Load X Number of Lines From Excel File

mato32188 — Thu, 11 Dec 2014 18:49:06 GMT

Hi noah,

use a RecNo() in where clause...WHERE RecNo() <=2000;

Re: Load X Number of Lines From Excel File

Thu, 11 Dec 2014 19:07:25 GMT

When I do

LOAD cccqs_0,
     cccqs_1,
     cccqs_2,
     cccqs_3,
     cccqs_4,
     cccqs_5,
     cccqs_6,
     cccqs_7,
     cccqs_8,
     cccqs_9,
     cccqs_10,
     cccqs_11,
     cccqs_12,
     cccqs_13,
     cccqs_14,
     cccqs_15,
     cccqs_16,
     RecNo()
FROM

(ooxml, embedded labels, table is Sheet3)
WHERE RecNo <=2000;

I get a Script Error that says "Field not found - <RecNo>.

Re: Load X Number of Lines From Excel File

Anonymous — Thu, 11 Dec 2014 19:08:06 GMT

in your script use First keyword before load statement...something like below:

MyTable:

First 2000

Load

....

from ....;

Re: Load X Number of Lines From Excel File

Anonymous — Thu, 11 Dec 2014 19:09:07 GMT

you are missing brackets after RecNo....it is a function. but simpler approach is using keyword "First"

Re: Load X Number of Lines From Excel File

Thu, 11 Dec 2014 19:11:41 GMT

Thanks! This works.

Re: Load X Number of Lines From Excel File

Thu, 11 Dec 2014 20:23:25 GMT

you can also use noofrows function