Split one specific column in datatable into unknown multiple columns with specific column name

Help Studio
1

image
image719×185 4.61 KB

I have a question. Above I have a last column with multiple colors.
I would like to split each color to its own column. And get the apropriate column name as below.
image
image784×191 5.51 KB

Do anyone know how to do that?
Maby I also should ad that the information comes from an SQL and is added in another program. Only reason for Excel is to show you the table. It really never leave the process to an external table.

2

Hi @Thaagaard_Elofsson

image
image775×720 24.9 KB

SELECT
    Item_number, Description_, Price,
    MAX(CASE WHEN rn = 1 THEN color END) AS Color1,
    MAX(CASE WHEN rn = 2 THEN color END) AS Color2,
    MAX(CASE WHEN rn = 3 THEN color END) AS Color3,
    MAX(CASE WHEN rn = 4 THEN color END) AS Color4
FROM (
    SELECT
        Item_number, Description_, Price,
        value AS color,
        ROW_NUMBER() OVER (PARTITION BY Item_number ORDER BY (SELECT NULL)) AS rn
    FROM uipath_sample
    CROSS APPLY STRING_SPLIT(Colors, ',')
) AS Subquery
GROUP BY Item_number, Description_, Price;
3

Create variable maxColors int32

  • Read Range into datatable
  • For Each Row in Data Table
    ** Assign maxColors = Split(CurrentRow(“Colors”).ToString,“,”).Count
  • Repeat Number of Times (maxColors) with index
    ** Add Data Column “Color + (index + 1).ToString”
  • For Each Row in Data Table (with index)
    ** For Each in Split(CurrentRow(“Colors”).ToString,“,”)
    *** Assign CurrentRow(“Colors” + (index + 1).ToString) = currentItem.Trim

The logic here is to first count how many ColorX columns need to be added, then add them. Then loop back through the data and split the values out to those columns.

4

I guess this is ment to insert in the SQL query from above where I get the data?

Or how can I run a query on a datatable in UiPath. I am not alowed to change the input SQL for sequrity reasons. the table above is only a ex. My table is a list of patients. Where I should split one column into many. There is already 15 columns in the table.

5

I have a problem here. When it is finished with row one and go to next row to it tells me that column “Color 1” already exists in the table. And that is correct due to that it was created the row before.

6

Put an If around the Add Data Column:

If NOT myDT.Columns.Contains(“Color” + (index + +1).ToString)

Also, a correction to my earlier post:

** Add Data Column “Color" + (index + 1).ToString