How to Convert Horizontal datatable to Vertical datatable
Input File :
Inputfile.xlsx (10.4 KB)
Both Name and Date in one cell, I have to fist split the cell values(name and date) and write in another DT in Below Format.
Expected output :
Follow this tutorial.
My requirement is different, In my case the Name and Date both in SAME CELL . first we need to first split the cell values
and update in different Vertical columns ( Name and date )
=> Read Range Workbook
Output → InputDt
=> Use the below code in Invoke Code activity
outputTable = New DataTable
' Define columns for the output table
outputTable.Columns.Add("Name", GetType(String))
outputTable.Columns.Add("Date", GetType(String))
' Read input data (assuming it's stored in a CSV or DataTable)
' Here, assuming inputTable is already populated with data
For Each row As DataRow In inputTable.Rows
For Each col As DataColumn In inputTable.Columns
Dim cellValue As String = row(col).ToString().Trim()
Dim newRow As DataRow = outputTable.NewRow()
newRow("Name") = System.Text.RegularExpressions.Regex.Match(cellValue,"[A-Z]{2,} \+\d+|[A-Z]{2,}\d+|[A-Z]{2,}").Value
newRow("Date") = System.Text.RegularExpressions.Regex.Match(cellValue,"\d+\-[A-Za-z]+\-\d+").Value
outputTable.Rows.Add(newRow)
Next
Next
Invoked Code Arguments:
=> Use Write Range Workbook to write it back to excel.
Workflow:
Hope it helps!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
