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
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
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
@bhanupriya.cb Can you provide a sample input excel
Hi, Please find the below sample file
Sample_file.xlsx (78.9 KB)
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…
give me a little time, will help you on the implementation
Hello @bhanupriya.cb ,
Here my suggestion is
Sure, thank you so much
ExcelDemoSearch.xaml (9.0 KB)
Sample_file.xlsx (134.9 KB)
Try the attached.
@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)
@bhanupriya.cb
we can fine tune and optimize as well
Read excel data / preperation of the column headers
cn = dtData2.Rows(0).ItemArray.Select(Function (x) x.toString)
dtData2.columns(cidx).columnName = cn
find indexes and split the into the tables
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
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)
Thank you it is working as expected
Thank you so much I tried your code also and it is working as i wanted
Hi Thank you so much, it is working as expected
Hi @bhanupriya.cb,
You can loop over datatable assigned after read range activity, check if condition where first column has string “deduction”.
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…
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?
@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
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.