Extensive utilization of Excel

Help
3

I had a similar process that used a single excel sheet and needed to create 2 tables of varying length. I posted a question about it here: Extract beginning portion of data table only

I went about it by doing the following:

  1. Read range on excel file and get the entire sheet into a single datatable variable.
  2. Find the first blank row after the 1st excel table & store as an integer variable “N” - this helped me know where to separate the tables.
  3. Clone the original table from step 1 into table1 and table2 datatable variables using an assign activity
  4. Use the datatable method .take and .skip to separate into 2 tables. To do this, assign RowsToAdd = datatable.AsEnumerable().take(N-1) or .skip(N+1). This should be of variable type IEnumerable Then use a for each to iterate over RowsToAdd and use the invoke method activity on the table1 and table2 variables using the ImportRow method

However, your case might be a bit easier. It looks like the first table you’d like is always going to be in cells A3:B8, correct? If so, then you should do the following.

  1. Read range on excel specifying range A3:B8 and that the data does not have headers
  2. Read range on excel specifying range A10:F10000 (or however many will definitely be more than the most rows you’ll ever have). Make sure to check the box that data contains headers
  3. Use a .Select statement on table from step 2 to get Blank rows
  4. Use a for each datarow on the datarow collection from step 3 and delete them