Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
Sure, but as far as I understand, this will only exclude specific rows rather than a range of rows.
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.
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!
Maybe this? The rest would be the same as before.
if(Field1='END',0,if(rowno()=1,1,peek(Keep))) as Keep