Hi All, need help in creating a logic for excel as part of overall workflow.
I need to run Logic to lookup the correct String based on the value in column CAN in first tab, and then replace values in the string based on the FY and OBJ.
I have an excel data which contains two tabs Final Data Extract and String. I need to get correct value of string from column Concatenated Segment under tab String comparing value from column CAN which exist in both sheets. Then two things need to be change in String values mapping FY and Object Class columns in Final Data Extract tab.
Once the correct string value is taken, need to add correct FY and OBJECT class in string vallue mapping from this two columns.
Then bot will use that updated string in later process.
I have attached Excel file.
Any help will highly appreciated.
Thank you112519_FY20_CAN_Extract_UFMS.xlsx (1.5 MB)
Hi Akshay,
Thank you for the response. Basically there is a Excel spreadsheet which has two tabs with multiple columns in each tab. The bot needs to find the correct value of string number from a column (Concatenated Segments) in s second tab (String). To find the the correct value bot needs to compare a value from column (CAN) in first tab (Final Data Extract) to value from column with same name in second tab (String). Then once it finds correct string, it saves the string in first tab with associated row. Now bot needs to change two things in string, FY and Object Class present in first tab.
For example: If a human does this, let’s copy a CAN number of first row in first tab (Final Data Extract), compare it with CAN number in second tab (String), you will find the similar CAN in second tab and its associated string number. Take the string number, copy it in first tab in first row. Then copy FY and Object Class and paste it in string number and save the excel.(please refer fourth screenshot to see what part in string FY and Object Class be pasted)
Hope this make sense. Also check Excel I have attached earlier.
Thank you
If i understand well ^^, you retrieve all excel files in different DataTable.
You can make a “lookup DataTable” activity, it will retrieve a datarow of the datatable you are looking for. (if there is a match. Just think to check it).
After, you can find your data in the datarow. Juste retrieve the line of orginal datatable you want to modify with something like “datatable.Rows.IndexOf(datarow)” in an assign activity.
Hi @vdarold
Thank you for responding. Right I have excel file retrieved to one data table. I don’t how to create rest of the logic I explained earlier. Could you please elaborate and explain step by step?
Thank you
1- Retrieve DataTable FinalDataExtract
2- Retrieve DataTable String
3- Foreach DataTable FinalDataExtract
3.1 - Use LookUpDataTable activity to retrieve the row when CAN match
3.2 - Retrieve the CONCATENATED_SEGMENT value from the DataRow
3.3 - Split CONCATENATED_SEGMENT by “.” - result a string array
3.4 - Replace position 1 and 4 in the string array by the value in the foreach datarow (Yeara and Object)
3.5 - Build your new variable CONCATENATED_SEGMENT with the array modified before (concatenation of each index of the string array using “.”)
3.6 - Update the result in the Excel File using DataTableString.Rows.IndexOf(DataRowString) to retrieve the current line in String tab
4 - End
Retrieve the CONCATENATED_SEGMENT value from the DataRow
Split CONCATENATED_SEGMENT by “.” - result a string array
Replace position 1 and 4 in the string array by the value in the foreach datarow (Yeara and Object)
Hi Akshay
Thank you very much for this solution. I have added it to my major workflow which I am automating. Its working. I can’t thank you enough. I really appreciate it. One last thing, could you please explain below highlighted conditions so next time if something like this or other comes up I can do my own?
Thank you very much again.
Kirtan
Hi @kirtan11patel,
It may be helpful to you. The package has activities called “Find” and “Find Replace”.
Using this you can able to achieve this.
Your solution takes correct string, creates column in correct tab, paste string in it replaces FY and Object but there is one issue. It also adds object class in other places as well. Please see screenshot below.
Anil_G
Yassine_LAMARTI