Ignore last row of Excel on LOAD

[schneider2037]

I have an Excel data source that comes with a merged cell in the last row that I want to ignore on LOAD

I feel there has to be an easy way to ignore this last row (which will be on a different row# every day) but can't seem to find the correct bits for "load everything from row 1 to last row minus 1."

Any help would be mightily appreciated!

[Anonymous · Accepted solution]

Hi,

you can try something like this:

Temp_Data:
LOAD

*,
rowno() as Recno
FROM
Excel.xlsx
(ooxml, embedded labels, table is Sheet1);

Find the last record form source:
LoadMax:
Load max(Recno) as Recno1 Resident Temp_Data;

Let vMaxNo=Peek('Recno1',0,'LoadMax');

Data:
NoConcatenate
LOAD
*
Resident Temp_Data where Recno<>$(vMaxNo);

Drop tables Temp_Data,LoadMax;

[marcus_sommer]

Quite probably could this row be identified with some content of 'Total' in any column or that there is some content missing like a date which could be checked with isnum() or len() or maybe FIELD <> '' or something like that. This means something like:

load * from excel where FIELD1 <> 'TOTAL';

- Marcus

[schneider2037]

Good thought, I'll give it a run!

[schneider2037]

Gotcha.

Will take a run with it.

Thanks!