I need some help in finding a solution to this challenging problem, I have to update some info in SAP from an excel file. The file has different columns which needs to be entered in different fields in the SAP GUI (Marked the headers of these fields in green background), except for few columns which needs to be updated in a table (Marked the headers of these fields with red background).
Another important thing to notice here is that the no of rows for each vendors differ like for the first vendor has 4 rows (stretches from row - 2:5).
Also for the excel sheet snip which i have shared has 5 vendors and so the SAP entry will be made in 5 iterations ( in first iteration the first vendor details will be entered and so on). I have marked all the vendors in 5 different colors to show that at a time in the SAP GUI only one vendor details will be entered.
Attaching these snips for reference -
Check the training below, I think in lesson 6 I am explaining how to enter the data from Excel into SAP
SAP Academy course will help you to learn the automation techniques and the best practices for SAP WinGUI automation
Best regards, Lev
Hi @shekhawat.arjun1307 ,
To achieve this you can follow below steps:
Read excel and store data in dt
1 - Read first column from read column activity.
2 - Remove empty rows and store the final result.
Now you have only vendors in collection.
3 - Iterate collection from for each loop
4 - Pass item in Filter Data Table to filter only rows of respective vendor (pass your “dt” in input and create new data table name “dtFilter” and pass it to output of filter activity).
5 - When you have new filtered rows in “dtFilter” then use another foreachrow activity to put respective vendor row in SAP.
Let me know if you have any doubt.
If you don’t want to use read column activity then another way is to get only column A in list and iterate one by one.
I have a doubt in the 4th step -
Firstly when i pass item(1st vendor name from collection) to filter data table activity the rows with no supplier name ( from excel sheet pic, row no - 3 to 5 for vendor abc) will be removed which should not happen.
Also if the second vendor also has the same name as the first one then in that case also the filter data table activity will keep 2 records whereas it should be only 1 per iteration.
Kindly suggest if there is some gap in my understanding if not how can we tackle these issues.
I got your point, for that you should first update empty vendors on run time and then use that dt further.
To achieve this particular step i have attached one xaml file below
ExcelLogic.xaml (9.1 KB)
Instead of highlighted read range you can pass your dt
Let me know if you face any issue.
The above solution will break if we have 2 consecutive vendors with same name, in that case this solution will assume that both the records are same.
Before updating empty vendors
After updating empty vendors(at run time)
My line of thought was that out of one big data table we have in the beginning, we separate out the chunks and then at each iteration take the next chunk, but i don’t know if it is possible.
Please let me know your views and how to solve the above issue.
I checked out the course but that is very basic, my use case is a little bit tricky. Can you give some inputs for it.
Their is an another way you can create one array of string and store vendors on run time, if same vendor exist in array then add vendor name appending 1 to it ex - AA_1. In this way you will have different vendors data.
Now get all unique vendors from dt and iterate one by one on it.
Rest things will remain same, to filter data with vendor name and insert only that data to Application.
I have accomplished until this point as shown below -
That is populating the empty Supplier Number column and add a numeric value if the item is repeating.
Can you help me with a workflow as to how now i can assign different fields to variables and a list in case of part number and quantity which can then be uploaded.
Now you can use below query to retrieve only unique value from data table.
dt.AsEnumerable().GroupBy(Function(a) a.Field(Of String)(“Supplier Name”)).Select(Function(a) a.First).CopyToDataTable()
Put your output in new data table and start iterating on it for each value in row.
The above query just removes the info which are repeating in the ‘Supplier Name’ column like below
Which i don’t want because i am missing out unique info from column ‘Part no’ and ‘No of boxes’.
What i want here after the first pic is to capture certain variables for each Supplier Name as below -
First supplier - abc
Invoice no - 789698
Invoice date - 31/08/2021
Purchase order no - 25878
Schedule agreement no - 98754
No of boxes - 69
Part no - [98758, 85698, 47896, 59321]
Quantity - 987
Vehicle Number - 789
LR Number - 8974
The above values should be captured in the first iteration and uploaded in SAP
subsequently in the second iteration i want the details of second Supplier info (abc_1)
to be uploaded in SAP. The main objective after reaching to the point of the first snip is to upload the data iteratively in the SAP.
Hope I was clear in the explanation.
Can you help me with this.
You took it in wrong way.
The query is to give you unique supplier name so that you can iterate and filter on original dt.
Store query result in separate dt and when you iterate it one by one their you filter on original dt and put filtered data in SAP.
Hope you get it.
I think you did not got the problem correctly, The supplier names are already unique, the issue is how to iterate and update the data into SAP because I have certain columns for multiple line items and some only single line items.
In that case you should hold the values of first row to be entered with second row during Iteration.
Let me know if you need help on this.
Yeah but how to do that, logic I am also able to think the issue is I am quite new to UiPath, Can you help with a workflow for this?
Sorry bro, i don’t have access to my laptop as i am out of town.
I can provide it on Monday but it’s to late for you.
Try if you get help from someone.
You can try below logic.
Put the body of this loop in your loop where you are iterating on filtered data.
you can provide the workflow by Monday if you want I can attach the excel sheet as well, Till that time let me try from the snip that you have provided.