Load partial data from Excel

[#1]

Have numerous excel 2010 workbooks stored in a folder "Days", each with 1 worksheet .  Worksheets may have the same or different sheet names across workbooks.  I want to load data beginning with row 42.  I have differing numbers of rows of data (beginning with row 42) across workbooks.

Here is the load statement:

LOAD filename() as JobNoSheet,

          rowno() as EventNo,

     From,

     To,

     Hrs.* 24 as DecimalHours,

     [Start Depth] as StartDepth,

     [End Depth] as EndDepth,

     CL,

     Code,

     [Time Breakdown] as TimeBreakdown

FROM

(ooxml, embedded labels, header is 37 lines);

This works fine, but I only want to load the data from rows where the value in "Hrs." is not blank.

How do I qualify the load statement to limit the data loaded?

Thanks

[Nicole-Smith · Accepted solution]

LOAD filename() as JobNoSheet,

          rowno() as EventNo,

     From,

     To,

     if(Hrs. <> NULL() AND Hrs. <> '', Hrs.* 24) as DecimalHours,

     [Start Depth] as StartDepth,

     [End Depth] as EndDepth,

     CL,

     Code,

     [Time Breakdown] as TimeBreakdown

FROM

(ooxml, embedded labels, header is 37 lines);

[#3]

Thanks

Adding

Where

not isnull(Hrs.);

At the end of the load statement also seems to work.

Is this equivalent to the code you suggested?

Bill

[Nicole-Smith]

Yup, that's the same thing, but mine also checks for empty strings.