yes you can load mutiple tables using below
from xls where rowno()<=10 ; ///( for example)
from xls where rowno()>10 and rowno()<=20
from xls where rownno()>20
you can take your rownos in xls.
but if your row increase in any of table after update will not come into it.
so i suggest you to take them in differents worksheets
and load normally
This is not an easy task but I've had to do something very similar in the past. Here's the steps I took.
1. Do a Transformation Load on the Excel sheet to get rid of duplicate blanks. Leave one blank row.
2. Get the Row numbers where there are blanks.
LOAD Row where isnull(A);
RowNo() as Row
In your Excel sheet You will get a value 2,7, etc. after the cross table.
3. Use the values of Row to create a range.
Load Peek(Row)+1as StartOfRange,
Row-1 as EndOfRange
This gives you a table where each row in the TableRowRanges table is a range of Rows to load for each table.
4. Use a loop function to loop through all the rows in the TableRowRanges table and use the values of StartOfRange and EndOfRange to set what rows to load using Where clauses.
The last step is going to take some coding work that I can't put together quickly tonight. This explanation is intended to give you a good starting place but not necessarily a complete code solution. Let me know if you have any questions and I'll do my best to answer. If it's helpful, please mark it as such. Thanks.