Datatable manipulation

Hi All,

I have datatable (dt) having names a,b,c,d,e. I have another datatable (dt1) which contains same column i.e. a,b,c,d,e but there many rows present in a and b in dt1.

here I have to take first two names i.e. a and b from dt and check them in dt1 and if found then copy the no of rows available between a and b in dt1 in separate sheet.

Please give me the logic.

Regards,
Shirish

Hi @Shirish
This is only a suggestion you can create dummy data of dt and dt1 and your desire output. to easily address the issue. Many Thanks. :smiley:

cheers :smiley:

Happy learning :smiley:

2 Likes

Hi @Shirish

Dt.Rows.First.CopyToDatatable()

Thanks
Ashwin S

1 Like

how . what it will give ?

sss.xlsx (10.0 KB)

Please find the attached input and output sheet

Hi @Shirish

Try the below code in assign activity, I hope it will help you

dt1 = dt.AsEnumerable().Where(Function(x1) dt1.AsEnumerable().Any(Function(x2) (x2.Field(Of String)(“ColumnA”)=x1.Field(Of String)(“ColumnA”) AndAlso x2.Field(Of String)(“ColumnB”)=x1.Field(Of String)(“ColumnB”)))).CopyToDataTable()

@Shirish
This scenario should be solvable with a Join statement between dt, dt1 on the column a,b and selecting dt1 rows for the maching case

Hi @Shirish

try this

(From p in dt.Select() where( From q in dt1.Select() where string.Join(",",q.ItemArray).Equals(string.Join(",",p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Thanks
Ashwin S

not working

@Shirish
I refered to your excel given from above. Your problem description is not reflecting on what you want to achvieve. Can please confirm following summary of the requirement.

*1 From dt the first value is to retrieve from Coumn A identified, that all other columns (B,C,) are empty
2 this Found Value (in your case “A”) is to use to retrieve all rows from dt1 that are matching following condition:
2.1 rows are below the row that have on Column A the value same as retrieve from 1 and other columns are empty
2.2 the rows to retrieve are limited till a column A value occurs and all other columns (B,C,D) are empty

Your task is solvable, but sure needs an another approach. We had done in the past with so called block detection logic. Let us know if you need further help

can you please provide me soln

I will have a look on it during my lunch break

@Shirish
PFA Initial Prototype:
Shirish.xaml (10.8 KB)

Kindly Note:
Rewire Path to Excel according your System
Reset the Blockfilter Variable Value (Variable Panel) to your Block (e.g. A)
Debug and use BreakPoints for inspecting what the implementation is doing.

Let us know your open questions. Mark the solving post as solution. So others can benefit from it

1 Like

@ppr

Thanks . ideally it should check for all rows of input sheet i.e.B,C,d,e and print all in output sheet. Is there a way ?

1 Like

@Shirish
You are asking if its possible to iterate over all Values from dt Column 1 (A,B,C…) and creating for each Block a separate DataTable?

Please find the attached sheet. it is not working for attached sheet. request you to check sss.xlsx (1.2 MB)

@Shirish
Please answer questions (Post 15)
Workflow was implemented with requirements and the xlsx from post above ~6

The Excel provided later differs heavy from communicated requirements and inputs and will and cannot work.

So, please clear your final requirements in advance

later attached excel should work