Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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
Yup, that's the same thing, but mine also checks for empty strings.