Workbook automation to non-conventional table format

Hello community!

I am a beginner with UiPath and RPA in general, and right now I am struggling with a use case.
For a bit of context, I performed a similar task with the same input file (attached) where I just moved certain data to other excel file in a table format similar to it. I was able to create that bot and the processing speed was acceptable for the users.
Now, I have to perform a similar action of getting data from the excel input and this time create an excel file from scratch in the form of the attached output. You will see that the format is of ROWS, the “table titles” are in the column A of the excel sheet and the information is appended per column instead of rows. The issue for me in this situation is that the logic I created seems to be performing considerably slow. It takes more than 10 minutes to process the file, and that is when it runs smoothly.
My main logic is the following:

  • Loop on the extracted dt from input file
    • Loop on excel file after column A
      • for each row of dt (perform operations) and set on the corresponding column
      • update for next column
        The thing is that I am updating the columns based on a list I created containing the names of the excel columns up until 17000 columns. A, AB, ABZ, etc.
        I am aware this logic might sound crazy but that is where my issue comes from. I do not know how to deal with this example.

Any help would be really appreciated.

Below the referred files:
input sample.xlsx (157.0 KB) output sample.xlsx (55.0 KB)

@andres.santos The Input and the Output Excel has data which cannot be mapped to get a Logical Explanation :sweat_smile: , How do you get the Brand Column Values and What is P?

:scream:

@supermanPunch hi, thank for your reply! :smiley:
I have added an extra column with the mapping, I hope is understandable, either way, as per your reaction :rofl: I know now that the list with the excel column names is far from best practice. Adding to that, I am looping in the way I described two times per each sheet in the input file. once for getting the “text” values and another loop for the the “price” values (last rows of each sheet in the output).

I forgot to mention but i am not using Excel activities, but rather Workbook since we have issues when running with the Excel Scope. output sample with mapping.xlsx (56.8 KB)

@andres.santos Is this Constant as well?
constant

@supermanPunch yes, in this case that can be considered constant, “2020” always.

1 Like

@andres.santos I was able to get the required output for the “Model Price” Sheet as the expected output it needed. I have used Linq Query along with For Each to get the matching rows and then append the values to a resultant datatable as needed. In addition to that a Transpose of the datatable was needed. Hence I have used one of the Transpose Datatable Activities which you need to download in order to Execute the Workflow.

Below is the Activity that I have used, Download it from the Manage Packages :

Check this Workflow :
GroupBy_Transpose.zip (150.2 KB)

Check the Input File. Make Sure the Transpose Datatable Activity is installed. Execute the Workflow. It Should generate an Output File as you needed it. Check if the Output is Proper, and the method that I have used is Consistent in terms of your use case.

The same can be done for the Other Sheet as well. Although the Grouping Condition differs. I am trying to automate the same but found one Confusion.


You might have missed one condition :sweat_smile:

1 Like

@supermanPunch Thank you!
I will check the workflow and i will let you know.
Sorry about that, yes i missed some info. here is below:

1 Like

@andres.santos Can you give me a feedback on the workflow that I had given earlier, Since the Equipment part is also based on that, I can continue to work if it’s the right way or else it will be a waste of time :sweat_smile:

Also Regarding the Output values of Equipment, there needs to be some clarification made :

For the Model Code ‘95BBL1’ and the Equipement Flag ‘F’ the Equipment value is ‘2Y’ . but in the output you have put the value as ‘U’. Why is that ?
data1

Hi @supermanPunch!
Yes, I just checked and is working perfectly as intended and super fast! I have to do a bit more research about Linq Query :sweat_smile:
Thank you.

Do not worry about this, is just that for that type of equipment the difned code is a letter U instead of the value in the Equipment column.

@andres.santos I can’t actually get a logical explanation to automate it if that’s the case :sweat_smile: I have also seen ‘U’ in some other places where it has some other Equipment Code.

@supermanPunch it is not logical for me either if you take into account the other mappings. This is because that Element Type “1” means is a type of equipment that for another system needs to be a “U” instead of the elemento code found in the file. This is an exception to the other rules lets say

If Element Type = 1 Then
Element Code = “U”

This logic might be able to help, but although we can’t be able to map it back into the input data by the Code values since there will be similar data, I’m not sure if this is a good Schema to be used as the output. I’ll try to get the Desired Output by using the conditions given.
data3

@supermanPunch You are right, I think a better way to define it is to say:

When “Equipment flag” in input file is “F” then two simultaneous conditions.

  1. in output > “ElementType” = “1”
  2. in output > “ElementCode” = “U” and this one also based on the equipment flag value of “F” instead of ElementType 1 in the output itself.

This is more logic and you based both values on the value from the input file directly

@andres.santos I was able to get the output for both, and I think the outputs are matching as I have checked with the Columns of the Expected Output file that you have given, and you should be able to get the outputs from the same workflow that is given below. This workflow provides both the Models and Equipment Output. So you can use this as the Whole Process. But make sure the outputs are matching and for execution again the Transpose Datatable Activity was able to help a lot.
GroupBy_Transpose.zip (150.7 KB)

1 Like

@supermanPunch :raised_hands: Thank you very much for the support and help. I learned a lot from this interaction!!

1 Like

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