Remove a range of rows based on condition

[#1]

Hi,

I have multiple, similar Excel files. When I grab the information from them, there is a header block in each, that I need to remove on the load. The problem is that the range of rows varies, BUT I have a reference point - the last row will always have the same string in the first column.

Do you know if there is a conditional range remove? Or maybe there is a work-around?

Thank you,

Siarhei K.

[#2]

You should be able to just include a where clause in your load script to eliminate your problem rows.

WHERE (first_field <> 'same_string'

     or first_field <> ' ') //whatever else you may need

[#3]

Sure, but as far as I understand, this will only exclude specific rows rather than a range of rows.

[johnw · Accepted solution]

So, what does this data look like?  Something like this?

Blah blah blah blah
blah blah blah blah
blah blah blah blah
END  blah blah blah
Ann  0123 blue 10
Bob  1234 red  15
Chad 9912 blue 50

You can't predict what the 'blah blah blah blah' looks like, and you can't predict how many lines of it there will be.  All you know is that the LAST line you need to ignore has something recognizable in the first field, in my example 'END'.

For this case, one way would be like this:

MyData:
LOAD
Field1
,Field2
,Field3
,Field4
,if(previous(Field1)='END',1,peek(Keep)) as Keep
FROM your file
;
INNER JOIN (MyData)
LOAD 1 as Keep
AUTOGENERATE 1
;
DROP FIELD Keep
;

I almost never load from files, so there may be a much simpler way of ignoring header information in files that I'm simply not aware of.

[#5]

Hi,

I would like to do the exact opposite.

I want to exclude all lines after a field contains a certain value.

How would I go about doing this?

Thanks in advance!

[johnw]

Maybe this? The rest would be the same as before.

if(Field1='END',0,if(rowno()=1,1,peek(Keep))) as Keep