Excel Query component - how do I set range to exclude the last row, when I don't know how many rows there will be?

Matillion ETL
1

I am using the Excel Query component in an orchestration job.

 

The file I am loading has come from a reporting tool. It has 5 rows of headers, then at the bottom of the data a row with the totals in it.

 

If I set the range to A5:J5698 it works perfectly, but obviously I don't know how many rows will be in it as it will vary each time.

2

.... sorry I submitted that before I was finished- any advice appreciated please, thanks!

3

Hello,

 

First ( which I not suggest ) is to scan the metadata of the excel with Python/Preload or whatever, to know the range and give it as a parameter.

 

Second, ( which I would do ) load everything into snowflake and also including the Excel 'rownumber', then in transformation remove the highest rownumber from the data before further usage.

 

Hope this gives ideas on going forward :)

 

Br,

Michael