Data Table Manipulation - Shifting Columns

Hello UiPath and data table manipulation gurus,

I need to realign some data in a Excel (please see s/c below).

BEFORE

AFTER

Currently, I loop through column C (“Number”) and determine whether it is a 6 digit value starting with H. Then = nothing; else = (where I’m stuck) I need to search horizontally on that row to find where the 6 digit H* value is, find its location, transfer it and all related data (to the right) starting in col C. . .

My initial thoughts were to just make nested if statements (yikes!) to search col D, E, F, G for the same “6 digit, H*” criteria and perform hotkeys like “[d(ctrl)][d(shift)][k(right)][u(shift)]x[u(ctrl)]” to cut and paste in col C. . . I’m assuming there is a much smarter way to do this with more concise code or an invoke method or something.

I’d greatly appreciate any thoughts on how best to approach this!

Thank you!
Shelby

shiftDataRow.zip (33.1 KB)

@Shelby_Pons Check this modified workflow :

Since you had provided the data, I managed to figure out the pattern in it, and I have used it accordingly to extract the right data and create the proper Datatable. There are some assumptions made

  1. I have used colorsArray that contains all the colors, as I thought the Colors Column will have only those Color values.
  2. Letter Column I have assumed to be only Single Capital letters.
  3. Number Column Extraction maybe proper because, It has a Specific Pattern.
  4. To get the Food Column Value, I have done a Not operation on the Other values Extracted
  5. Also I have considered the first 2 Columns are proper and don’t need data replacement.

If these assumptions are proper, then you may get the right output always for other kinds of data. Else will have to modify it accordingly.

Check the Input File. I have added a Template Sheet to get the format of the Output. Close the Excel. Execute the Workflow. It should generate the output in the same excel Sheet.
shiftDataRow.zip (34.9 KB)

2 Likes

Hi,

How would you modify the functions if the first 4 columns are proper and don’t need replacement? In this case, there are 2 additional columns before the Number column. Also, what will change in the function if the Letter column contains more than one character of both letters and numbers?

Thanks,

Emmanuel

@Emmanuel_Gyeni If the Column patterns are unique we might be able to get the correct values but if they are not, then there won’t be a very accurate method to identify the right value for the right column :sweat_smile:

In this case the column patterns are unique. For instance, same exact data but there is a “favorite restaurant” in the 3rd column and “location” in the 4th column. The “number”, “letter”, “color”, and “food” columns are in the 5th, 6th, 7th and 8th columns respectively. How will the syntax to move the same exact data vary from the syntax you used? How can I modify the syntax in this case?

@Emmanuel_Gyeni I do not think there would be many changes if the Four Columns that you would need to make proper are the same, but if instead of 2 there are 4 Proper Columns i.e the Starting 4, then you could add an extra method when using the SelectMany Query i.e use row.ItemArray.Skip(4).SelectMany(). In this way it will Skip the first 4 Column Values and then Perform a Search on the rest of the columns. Also the template Files you need to keep as you need. You can also set the conditions according to your preference in the SelectMany Clause.

Please take a look at a sample data like this

How will the syntax be modified to suit this scenario?

@Emmanuel_Gyeni I am not fully sure about the different aspects that are present in the excel, Because I was Assuming things for the previous workflow I built. I may have to assume some patterns for the Columns or you can provide the patterns for Each Column, then I may alter the existing workflow and provide you the Updated one.

Although I haven’t got a feedback from @Shelby_Pons I think it should work properly for your case as well.

Can you provide that as Excel File or do you wish to modify the workflow by yourself ?

Shift row.xlsx (17.4 KB)

Attached is the Excel File.

I tried modifying the workflow and have been unsuccessful. And by the way, I am working with @Shelby_Pons

2 Likes

@Emmanuel_Gyeni Ok. It is understood that there is a pattern for Number Column, But I do not know the Patterns for Food and Region Columns, i don’t think there will be a Specific pattern For them :sweat_smile: Can you Confirm it ?

One difference I have noticed is the Alphabet Case, if Region has Always Upper Case letters and Food has only Lower case letters then we can make it work

Both the food and region column seems to have a mixture of lower and upper cases actually. No other specific pattern. But they will always be in the same columns. They both always seem to shift to the right when the number column shifts to the right :sweat_smile:

@Emmanuel_Gyeni Ok. One more pattern can be recognised as the Last filled Column will always be the Region Value and One Column Less than that will be the Food value. If you think this can be a pattern then we can go with this logic. Otherwise I don’t see any other method, except by making a list of possible regions and checking if the value is present in it :sweat_smile:

1 Like

Looks like we have no other option but to try out that logic :sweat_smile:

@Emmanuel_Gyeni Hey, Can you give me a pattern for the Number Column, Seems like the Pattern I used earlier didn’t work, as It Contains HQ2312, I only thought there would be H at the beginning :sweat_smile:

The only pattern I have noticed is that it always seems to start with “H” and 6 characters long

@Emmanuel_Gyeni Then I’ll go with it for now, You can adjust it according to your requirement later.

@Emmanuel_Gyeni Check this Workflow :
shiftDataRow.zip (33.7 KB)

1 Like

Can you please explain this function and what it’s supposed to accomplish. I have been unable to figure it out thus far. row.ItemArray.Skip(4).SelectMany(Function(x,i)if(System.Text.RegularExpressions.Regex.IsMatch(x.ToString,“H[a-zA-Z\d]{5}”),{i},{})).ToArray(0)

@Emmanuel_Gyeni This is inside a For Each Row, So Each row will be Looped over.

The row variable has all the column values. To be able to access those values in a index manner, We are performing a conversion to an Array form using ItemArray, then we use Skip(4), to Skip the first 4 Column values since those are the Proper Values.

The SelectMany clause is used to get the List of indices that match the Regular Expression for each of the other column Values. Since it is always one. I have narrowed the Search to (0)

1 Like