extract multiple tables from a single sheet

[#1]

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

[#2]

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

[SunilChauhan]

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

[#4]

yes, correct to the point

[#5]

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

[trey_bayne]

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.

[#7]

great, ill try it out