Split value from one column and update in another new column - Data table

[Sathish_Kumar_S]

This table scrapped from website :

Input File :
InputFiles.xlsx (11.8 KB)

Need to split value from “Column-0” and same row value in “Rating” column and update in new column “subheader” as last column

NOTE : Two values in “Rating” Column just want to split the value available in the same row next to column-0

Keep the remaining value under “Rating” Column

For Example :

Value “1” from Column-0 and Value “Supplier Site Identified.” from Rating -->combine both values and update new column (subheader)

Keep the “Complete” value in “Rating” Column

Remove column-0

Expected output

ExpectedOutput_New.xlsx (11.5 KB)

@Parvathy

[yedukondaluaregala]

@Sathish_Kumar_S

After reading the excel
for output datatable insert one column.
use for each row in datatable activity
inside loop get two column values by taking two assign activities, and use write cell activity.
and pass the value by using concatination Variable1+". "+Variable2

try and let me know if any additional info required

Happy Automation!!

[Sathish_Kumar_S]

If you noticed there is a another value is there in “Rating” column.. I need to keep that value in “Ratings” Column.

Just need to cut the value from Column-0 and value in same row under Rating column

Please check the Expected output excel file

[Parvathy (PS Parvathy)]

Hi @Sathish_Kumar_S

=> Read Range Workbook
Output → InputDt

=> Use the below code in Invoke Code activity

OutputDt = New DataTable

OutputDt.Columns.Add("Rating", GetType(String))
OutputDt.Columns.Add("Required Date", GetType(String))
OutputDt.Columns.Add("Planned Date", GetType(String))
OutputDt.Columns.Add("Actual Date", GetType(String))
OutputDt.Columns.Add("Action Plan Available", GetType(String))
OutputDt.Columns.Add("Evidence Required / Attached", GetType(String))
OutputDt.Columns.Add("Last updated By/On", GetType(String))
OutputDt.Columns.Add("subheader", GetType(String))

Dim lastSubheader As String = ""

For Each row As DataRow In InputDt.Rows
    Dim numberPart As String = row("Column-0").ToString().Trim()
    Dim descPart As String = row("Rating").ToString().Trim()

    Dim rating As String = row("Rating").ToString().Trim()

    ' Handle Date fields safely
    Dim reqDateRaw As String = row("Required Date").ToString().Trim()
    Dim planDateRaw As String = row("Planned Date").ToString().Trim()
    Dim actualDateRaw As String = row("Actual Date").ToString().Trim()

    Dim reqDate As String = If(Not String.IsNullOrEmpty(reqDateRaw),
        DateTime.ParseExact(reqDateRaw.Split(" "c)(0), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-yyyy"),
        "")

    Dim planDate As String = If(Not String.IsNullOrEmpty(planDateRaw),
        DateTime.ParseExact(planDateRaw.Split(" "c)(0), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-yyyy"),
        "")

    Dim actualDate As String = If(Not String.IsNullOrEmpty(actualDateRaw),
        DateTime.ParseExact(actualDateRaw.Split(" "c)(0), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MMM-yyyy"),
        "")

    Dim actionPlan As String = row("Action Plan Available").ToString().Trim()
    Dim evidence As String = row("Evidence Required/Attached").ToString().Trim()
    Dim updatedBy As String = row("Last updated By/On").ToString().Trim()

    Dim isDataEmpty As Boolean = String.IsNullOrEmpty(rating) AndAlso
                                 String.IsNullOrEmpty(reqDate) AndAlso
                                 String.IsNullOrEmpty(planDate) AndAlso
                                 String.IsNullOrEmpty(actualDate) AndAlso
                                 String.IsNullOrEmpty(actionPlan) AndAlso
                                 String.IsNullOrEmpty(evidence) AndAlso
                                 String.IsNullOrEmpty(updatedBy)

    ' Build subheader
    Dim currentSubheader As String = ""
    If Not String.IsNullOrEmpty(numberPart) AndAlso Not String.IsNullOrEmpty(descPart) Then
        currentSubheader = numberPart & ". " & descPart
        lastSubheader = currentSubheader
    End If

    ' Skip row if both Rating and subheader are present
    If Not String.IsNullOrEmpty(rating) AndAlso Not String.IsNullOrEmpty(currentSubheader) Then
        Continue For
    End If

    ' Add only rows with valid data
    If Not isDataEmpty Then
        Dim newRow As DataRow = OutputDt.NewRow()
        newRow("Rating") = rating
        newRow("Required Date") = reqDate
        newRow("Planned Date") = planDate
        newRow("Actual Date") = actualDate
        newRow("Action Plan Available") = actionPlan
        newRow("Evidence Required / Attached") = evidence
        newRow("Last updated By/On") = updatedBy
        newRow("subheader") = If(currentSubheader <> "", currentSubheader, lastSubheader)

        OutputDt.Rows.Add(newRow)
    End If
Next

Invoked Code Arguments:

=> Use Write Range Workbook to write it back to excel.

Workflow:

XAML:
Sequence.xaml (11.9 KB)

Hope it helps!!

[yedukondaluaregala]

@Sathish_Kumar_S

Got it use below file
Sequence.zip (3.1 KB)