Copy the row from selected columns to a new datatable

Help
1

Hi,

I have a datatable,dt1, and try to filter a row and copy the selected columns to a new datatable, dt2. For example, I try to filter Apple, and then copy only the values in columns B,C,D to a new datatable. I can run a loop to find the row of Apple, but don’t know how to write only the corresponding columns. Can I use CopyToDatatable() to pick the columns ?

image

Thanks a lot.

2

When you loop through the Data Table Dt1,
1.> Use Get Row Item Activity(Use - Column Index = 0 in Properties pane) and you will be able to find “Apple”.
In Get ROw Item, create output variable - lets say Col1_Data

2.> Use If Clause - IF Col1_Data.ToString = “Apple”
then
Use Get row Item Activty and Use - Column Index = 1 in Properties pane - This will give the data present in Col B I.e 20

In Get ROw Item, create output variable - lets say Col2_Data

Similarly,
Use Get row Item Activty and Use - Column Index = 2 in Properties pane - This will give the data present in Col B I.e 34
In Get ROw Item, create output variable - lets say Col3_Data

Use Get row Item Activty and Use - Column Index = 3 in Properties pane - This will give the data present in Col B I.e 34
In Get ROw Item, create output variable - lets say Col4_Data

Now Send a hot key Shift + F11 - > this will create a new sheet blank.

Use write cell activity
Write Col1_Data in “A1”
Write Col2_Data in “B1”
Write Col3_Data in “C1”

Now Use read Range activity → and specify this sheet as the sheet Name from which you want to read the range.

This Read range will read all the data that you have written in the new Sheet that you have created and it will
Store it in a data table.

1 Like
3

Hi Prassin,

Thank you very much, it is indeed very clear and detail with step by step guideline. And for sure it works. :grinning:

May I also ask if the CopytoDatatable() can only copy the whole datarow to the datatable ? Just wonder if the argument of it could do anything in picking the elements in the datarow.

4

Hi @Snowman,

I think CopytoDatatable() will copy the entire content of the datatable to another datatable.

I am not sure whether we can do selective copying of data from one Datatable to another Datatable using CopytoDatatable() function.

Maybe we can use for loop and loop through the data table and exclude the one’s that we do do not wish to copy.

5

Appreciated, and thanks.

1 Like
6

@Snowman

First Read the excel sheet and store it in a datatable Dt

Then filter the datatable based on your category

Dt1=(From p In Dt.Select
where Convert.ToString(p(0)).Equals(“Apple”)
Select p).ToArray.CopyToDataTable()

Dt2=Dt1.DefaultView.ToTable(False,Dt1.Columns(1).ColumnName,Dt1.Columns(2).ColumnName,Dt1.Columns(3).ColumnName)

You will get the required result.

Regards,
Mahesh

1 Like
7

Hi @MAHESH1,

Great ! Nice to learn one more way to the problem, and good to add more tools in my bag.

Thanks a lot.