Exclude records containing empty or NULL during load

[kim_rormark]

Hi,

In my project I am loading data from a excel-sheet. The sheet contains ~150k rows, and in this application no records containing NULL or is empty in ANY cell are not allowed and should be excluded during load.

Is there a good way to do this?

- Kim

[#2]

Hi,

a rapid solution culd be a where in this way:

Load *

from .....xls()

where len(trim(FIELD))>0;

C u,

Stefano.

[its_anandrjs]

Hi,

Load  some thing like this in your code

LOAD

ColA,

Value

FROM

Data.xlsx

Where isnull(Value)=0 and Value>0;

to exclude 0 and blank space also it will remove both from the load

Regards,

Anand

[its_anandrjs]

Hi,

See the attached sample data is loading some thing like this

Temp:

LOAD ColA,

     Value

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet29);

Data:

Load

ColA as Field1,

Value as Field2

Resident Temp

Where isnull(Value)=0 and Value>0;

Let me know if not works

HTH

Regards,

Anand

[kim_rormark]

Hi Anand,

I think your solution will work, except for that ".... and Value > 0" will exclude zero-values (0), that are allowed. Only NULL and blank should be excluded. Could this maybe be fixed with ".... where isnull(Value)=0 and not Value="";" or something like that?

[kim_rormark · Accepted solution]

Solution was what you said, Anand, except I had to change the Where to ...  where isnull(value)=0 and not value='';

Thanks!