i get monthly reports from the security departments where there are a few tables in the sheet. the column names are consistent but the amount of data are not.
but the thing about the tables is that it can start anywhere and i cannot define a cell... i know how to get data when they are laid horizontally using textbetween, right, left etc but is there anyway to get the data the same way when they are arranged horizontally.
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.