How to split excel cell data dynamically?

Help Studio
, , , ,
1

Hi Community,

I have this data in excelrows
image

I have to get the values of A and B dynamically.( Excel Values may differ.)
And update the values in the portal in respective fields.

Example1 : If this is the portal having fields A, B, A and input value is
A-20,50 B-300,400 then it has to enter data in below format
image

Example2 : If portal is having fields A, B and input value is A-20 B-300 then it has to enter data in below format
image

Example3 : If portal is having fields A, A, B, A and input value is A-20,50,55,77 B-400
then it has to enter data in below format
image

So the fields in the web page are dynamic and also excel data is dynamic.

Can someone please help me with the logic.

Thanks in Advance:-)

2

Hii @Vaishnav_Tej ,
Please check this workflow and let us know this is your required output.

ExcelAutomation.zip (2.5 KB)

Input:
image

Output :
image

Hope this helps,

2 Likes
3

Hi @Vaishnav_Tej ,

It seems that you would require to re-arrange the values of the Excel to a different format.

Maybe you could try the below steps :

  1. Prepare an Output Datatable using Build Datatable activity having two columns, Preferably String or Object types. Let’s name it as OutputDT.

  2. We can then use the below Expression to get the data in a Table format as required :

OutputDT = (From s In DT.AsEnumerable.SelectMany(Function(x)Split(x("Your Column Name").ToString)).toarray 
Let firstValue = Split(s,"-")(0).ToString 
Let secondValueArray = Split(Split(s,"-")(1),",").ToArray 
From val In secondValueArray 
Select OutputDT.Rows.Add(firstValue,val)).CopyToDataTable

Here, DT is your Datatable from Input Excel, You would need to provide the proper column name in place “Your Column Name” in the expression which contains the values to Split.

Let us know if you were able to implement this.

1 Like
4

Hi,

Thank you for your workflow. I’m able to fetch the values but actual rewuirement is I need to enter those values in the WebPage portal. How can i do that dynamically?

As I cannot share Web Page screenshot due to confidentiality, I have shown you in the example how the page will have dynamic fields on every entry.

Please refer my examples and help me please.

5

Hii @Vaishnav_Tej ,
I have one query, I just wanted to know in the webpage how many slots will be there to update the values of A and B.
If only one each is there for A and one for B,’
then how you want the result of A to be printed because in the output if your input is like
A-20,50 B-300,400
Then your output will be
A 20
A 50
B 300
B 400
You want me to add all the values of A aswell as B or You want me to Type the values seperately?

6

@Vaishnav_Tej ,

Additionally let us know how the fields are presented in the portal, Is it available in a Tabular format ?

We should be able to identify the field names at first either by performing Table Extraction and get the data or by using Find Children activity and get the list of fields.

Then according to this data, we can filter the Resultant Output we have created accordingly and then enter the data into the portal.

Let us know some more details of the representation of the Fields in the portal.

7

For Eg. Consider this excel
image
P1 has 4 lines
P2 has 2 lines
P3 has 5 lines

And this is the WebPage. Here 4 lines are there. So it should consider Product P1
A-20,50 B-300,400

FYI- A means Discount and B means Net Price

As shown in the screenshot it has to enter respective values

image

8

@Vaishnav_Tej ,
I have updated my workflow according to your requirement.I have left select item and type into activity just by adding variable didnt indicate since the webpage is not available you can make the selectors dynamic and check.

ExcelAutomation.zip (3.3 KB)

Regards,

6 Likes
9

ok let me try. Thank you

10

Can you help me with the logic on uploading the data to the webpage based on no. of lines ?

11

@Vaishnav_Tej ,
Sure

1 Like
12

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