How to divide the 2 datatables from one sheet to different sheet

Help Activities
1

Hi Team,

Kindly find the below screenshot FYR, i need to divide the below 2 datatables using empty row or deduction string as a separator and write it in two different sheets, rows are dynamic so please tell me how to achive this

image
image417×604 14.5 KB

2

Hi @bhanupriya.cb,

Are the column names fixed?

Regards,
MY

3

One strategy could be:

  • calculate all row indexes for the EMP ID column with value EMPID (read range without add headers)

  • slice from the found row index all rows up the first empty EMP ID value is found

In general we can do the slicing with Skip and TakeWhile method

For handling the datatable column names we can e.g. use incorporate some clonings of an already prepared dynamicly constructed template datatable

Let us know if you need further help once you started the implementation

4

@bhanupriya.cb Can you provide a sample input excel

5

Hi, Please find the below sample file

Sample_file.xlsx (78.9 KB)

6

Hi PPr,

Thanks for your reply,

I am able to understand the first part but slicing is quite difficult to implement it can u please share me a sample workflow…

7

give me a little time, will help you on the implementation

8

Hello @bhanupriya.cb ,

Here my suggestion is :grinning:

  1. use lookup datatable activity and get the index of the Occurrence of Deduction. Suppose indexVal
    2)Use Read Range activity and for the first table it will A1:C(indexVal-2). to get first table
    3)use find first and last free row. supposr last row as lastrowVal
    3)Read Range Activity and A(indexVal+1):C(lastrowVal)
9

Sure, thank you so much

10

ExcelDemoSearch.xaml (9.0 KB)
Sample_file.xlsx (134.9 KB)

Try the attached.

11

@bhanupriya.cb Try attached workflow. I am writing data to the different sheets in the same excel. You can modify it accordingly

Example.zip (74.2 KB)

12

@bhanupriya.cb
we can fine tune and optimize as well

Read excel data / preperation of the column headers
grafik
cn = dtData2.Rows(0).ItemArray.Select(Function (x) x.toString)
dtData2.columns(cidx).columnName = cn

find indexes and split the into the tables
grafik
arrIndex =

(From i In Enumerable.Range(0, dtData2.Rows.Count)
Where dtData2.Rows(i)("EMP ID").toString.Trim.Equals("EMP ID")
Select x = i).toArray

TableList =

(From i In arrStartIDX
Let tbl = dtData2.AsEnumerable.Skip(i+ 1).TakeWhile(Function (x) Not String.IsNullOrEmpty(x(0).toString.trim)).CopyToDataTable
Select t = tbl).toList

grafik
grafik826×348 71.8 KB

So the approach is dynamic and driven by the column header line. In case that more tables will be present within the sheet, it will recognize it.

Afterward just iterate over the tablelist and write out the different tables to excel or process it.

Find starter help here:
ppr_ExcelBox_SplitExcel_ManyTablesInOneSheet_SplitByHeaderToEmptyRow.xaml (9.9 KB)

13

Thank you it is working as expected

14

Thank you so much I tried your code also and it is working as i wanted

15

Hi Thank you so much, it is working as expected

16

Hi @bhanupriya.cb,

You can loop over datatable assigned after read range activity, check if condition where first column has string “deduction”.

  1. Assigned this row number to some variable(deductRow).
  2. Two separates read range for same sheet with reading cell with the help of deductRow.
  3. First read range can be read from A1:C+deductRow, second can be read from
    A+deductRow+2:C+dt.RowCount+2

Note: +2 is used due to different row number logic of excel and datatable, it can be changed based on you are taking header, etc…

17

Hy but one small doubt actually in the sample data i have added only few columns, so i have almost 24 columns but it is considering only 13 columns hw do I copy everything?

18

@bhanupriya.cb Change the column in the code. Based on the sample data, we are considering from A to L. If it is from A to Z replace L with Z in the code

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.