Assigning values in datatable (data scraping) to variables

[SH_94 (Jacob SH)]

Hi Community,

I would like to ask how can i split the datatable ( extract from web using data scraping tool) and change it into variable?

For example,

Below is from data scraping tool (web browser- not from Excel)

Column 1 Column 2
AA BB
DD ZZ

I want to assign the column 1 to variable K and column 2 to variable M

May i know how can i spit it ?

Many thanks

[Palaniyappan (Forum Leader)]

Hi

We can use default view method

That is if the datatable is saved in a variable named dt once after data scrapping then use a assign activity like this

dt_1 = dt.DefaultView.ToTable(False,”Column-1”)

And another assign activity like this

dt_2 = dt.DefaultView.ToTable(False,”column-2”)

This will save as two different datatable

Hope this would help you

Or
you can get them as two different data columns like this

In a assign activity
dt_col1 = dt.Columns(0)

And another assign with
dt_col2 = dt.Columns(1)

Where both dt_col1 and dt_col2 are of type System.Data.DataColumn

Cheers @SH_94

[Srini84 (Srinivas)]

@SH_94

You can use as suggested by @Palaniyappan

datatable.DefaultView.ToTable(False,“Column Name”)

Hope this will help you

Thanks

[Srini84 (Srinivas)]

@SH_94

Also you can use Filter Datatable as below

Output Datatable as different other datatables as dt2

Hope this will help you

Thanks

[SH_94 (Jacob SH)]

Hi @Palaniyappan ,

My objective or next step actually plan to add the variable K and variable M into the " add data row" function as per screenshot below. May i know if the approach work?

So the outcome i try to achieve is under the Add Data Row activity, i will put as below
{ Outcomes, Outputattachment, Variable K, Variable M}

Is it possible to do it in this way?

Many thanks…

[Palaniyappan (Forum Leader)]

Yeah we can but with slight change

@SH_94

Outcomes, Outputattachment, Variable K, Variable M

Say for example if you have only one record in that main datatable and if it is split into two different datatable named dt_1 and dt_2 as mentioned above

@Palaniyappan

dt_1 = dt.DefaultView.ToTable(False,”Column-1”)

And another assign activity like this

dt_2 = dt.DefaultView.ToTable(False,”column-2”)

Then one add Datarow activity is fine with this expression
{Outcomes, Outputattachment, dt_1(0)(0).ToString, dt_2(0)(0).ToString}

Here we are trying to add the value in those two variables because we won’t be able to pass that variable itself being a datacolumn or datatable variable where as add Datarow activity accepts only string or individual object variable

If there are many rows in main datatable then after splitting them into two datatable like dt_1 and dt_2

Then use a assign activity like this
counter = 0

Where counter is a int32 variable defined in variable panel
Next use a WHILE Loop like this

dt_1.Rows.Count > counter

Inside the loop use a add Datarow activity where mention like this

{Outcomes, Outputattachment, dt_1.Rows(Counter)(0).ToString, dt_2.Rows(counter)(0).ToString}

Next inside the loop use a assign activity to increment the counter value
Counter = counter + 1

This will add all value from main datatable which is now splitted into two dt

Cheers @SH_94

[SH_94 (Jacob SH)]

Hi @Palaniyappan ,

I try to change it to variable and it give me the error as per screenshot below.

.

May i know if something that i have made mistake?

Many thanks again.

[Palaniyappan (Forum Leader)]

@SH_94

May i know if something that i have made mistake?

The variable type you have is datatable
But the expression used here will give Datacolumn as output type

If we want datatable then use this expression

@Palaniyappan

dt_1 = dt.DefaultView.ToTable(False,”Column-1”)

And another assign activity like this

dt_2 = dt.DefaultView.ToTable(False,”column-2”)

Or

If you want to have them as datacolumn then use this

@Palaniyappan

you can get them as two different data columns like this

In a assign activity
dt_col1 = dt.Columns(0)

And another assign with
dt_col2 = dt.Columns(1)

Where both dt_col1 and dt_col2 are of type System.Data.DataColumn

Cheers @SH_94

[SH_94 (Jacob SH)]

Hi @Palaniyappan ,

After i change it to string, the result only capture first row as per screenshot below.

@Palaniyappan

Then one add Datarow activity is fine with this expression
{Outcomes, Outputattachment, dt_1(0)(0).ToString, dt_2(0)(0).ToString}

Basically one result it may contain multiple row and currently it seem like string unable to capture complete row.

Thank you.

[Palaniyappan (Forum Leader)]

@SH_94

Basically one result it may contain multiple row and currently it seem like string unable to capture complete row.

Fine if it has multiple rows I have provided the steps for that as well

@Palaniyappan

If there are many rows in main datatable then after splitting them into two datatable like dt_1 and dt_2

Then use a assign activity like this
counter = 0

Where counter is a int32 variable defined in variable panel
Next use a WHILE Loop like this

dt_1.Rows.Count > counter

Inside the loop use a add Datarow activity where mention like this

{Outcomes, Outputattachment, dt_1.Rows(Counter)(0).ToString, dt_2.Rows(counter)(0).ToString}

Next inside the loop use a assign activity to increment the counter value
Counter = counter + 1

This will add all value from main datatable which is now splitted into two dt

@SH_94

[SH_94 (Jacob SH)]

Hi @Palaniyappan ,

Many thanks for the guidance .I just understand what u have mentioned.

However, this round it give me data with a lot of duplicate. Technically it should have 10 rows only but it give me 20 rows with duplicate .

Many thanks for the help.

[Palaniyappan (Forum Leader)]

@SH_94

Technically it should have 10 rows only but it give me 20 rows with duplicate

Check with the loop where it is adding the Datarow again
We can run in debug mode to find how it is getting repeated

Cheers @SH_94