Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
ive attached a sheet of sample data
thank you in advance
Akhila de Costa
Akhila,
i understand, that you dont know how many rows you have in each table.
But i think, you know:
- what tables should be in your spreadsheet,
- what are column names for each table
- in which excel column (A,B,C, etc) your business column (Doman/Virus, ect) is localised
Am i right?
Regards
Darek
yes you can load mutiple tables using below
Load
from xls where rowno()<=10 ; ///( for example)
Load
from xls where rowno()>10 and rowno()<=20
Load
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
yes, correct to the point
yea, i amn aware of that method but the thing is im not the one making these reports and this is the format they send it in... so i have a figure out away to get it done
thankyou anyway
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.
GetNullsTable:
LOAD Row where isnull(A);
LOAD A,
RowNo() as Row
Resident CrossTable;
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.
TableRowRanges:
Load Peek(Row)+1as StartOfRange,
Row-1 as EndOfRange
Resident GetNullsTable;
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.
great, ill try it out