How to convert required columns in to rows

[kmaddikatla]

Hi there,
please find the below attachments for Your reference

@ppr
@Yoichi
@ushu
@vishal.kp
@supermanPunch
@Anil_G
@rlgandu
@jose.ordonez1

Happy Automation ,

regards,
@kmaddikatla

[jose.ordonez1 (Jose Ordonez)]

Hi kmaddikatla,
Try Checking on the methods present in the below post :

How to Transpose Excel data or Data Table?

Hello UiPathans Here we discuss how to Transpose the data in Excel or a data table What is meant by Transpose? Input [image] Output [image] Transpose can achieve by two methods → Modern activity- Using Copy/Paste range Activity.(For Excel Processing) → Novigo Solutions. (For Datatable Processing) Modern activity- Copy/Paste range Activity. By Using modern activity we can achieve the transpose method. For classic users can select Show Modern from the Activit…

Cheers!

[Jayesh_678 (Jayesh)]

Try this:-

// Assuming dt is your DataTable variable

// Create a query to select the transposed data
Dim transposedData = From dc In dt.Columns.Cast(Of DataColumn)()
Select New With {
.ColumnName = dc.ColumnName,
.RowData = dt.AsEnumerable().Select(Function(row) row(dc))
}

// Create a new DataTable to store the transposed data
Dim transposedDataTable As New DataTable()

// Add columns to the transposed DataTable
For Each columnData In transposedData
transposedDataTable.Columns.Add(columnData.ColumnName, GetType(String))
Next

// Add rows to the transposed DataTable
For rowIndex As Integer = 0 To dt.Rows.Count - 1
Dim newRow As DataRow = transposedDataTable.Rows.Add()
For Each columnData In transposedData
newRow(columnData.ColumnName) = columnData.RowData(rowIndex)
Next
Next

[supermanPunch (Arpan)]

Hi @kmaddikatla ,

Could you maybe let us know how are the values added in the Output format, could you explain a bit more on the logic used for the Output required ?

If with the assumption made, datatable needs to be split into 4 columns of data, then the below expression could be used :

OutputDT = DT.Columns.Cast(Of DataColumn).Select(Function(c)c.ColumnName).Chunk(4).SelectMany(Function(x)DT.DefaultView.ToTable(false,x).AsEnumerable).CopyToDatatable

Here, OutputDT is a datatable variable and DT is the datatable which consists of the Input data.

Implementation :

In the Implementation above, the Read Range activity Add Headers Property is Unchecked considering that there are no headers for the data or there are duplicate values for the headers.

From Debug :

The Output could be further modified to meet the same column name format. However, we would require to verify the data values if they are in the Expected format or columns to proceed further if needed.

[kmaddikatla]

Hi @supermanPunch ,
thank you for the response,

in the 4th row of output(including headers), mumbai & MH repeating two times. that should also to be handled. it have to come one time .

Regards,
@kmaddikatla

[supermanPunch (Arpan)]

@kmaddikatla

in the 4th row of output(including headers), mumbai & MH repeating two times.

@kmaddikatla ,

The Implementation shared was based on the input data that you had provided, Could you double check on whether the Input data provided was the right data ?

And if needed to remove the duplicates, what is the removal condition, is it just remove duplicate values in the data ?

[Shiva_Nikhil]

@kmaddikatla

can you use this query, in my expression you need to create a build data table with 4 column names so that it will work

dt.Rows(0).ItemArray.Skip(4).Chunk(6).Select(Function(chunk) dt2.rows.Add(chunk.Distinct().ToArray())).copytodatatable

[Parvathy (PS Parvathy)]

Hi @kmaddikatla

=> Build Data Table

Output-> transposedDataTable

=> Read Range workbook. Remove Add Headers option in the properties Panel.

Output-> inputDataTable

=> Use below syntax in Assign:

transposedDataTable = inputDataTable.Rows(0).ItemArray.Skip(4).Chunk(6).Select(Function(chunk) transposedDataTable.rows.Add(chunk.Distinct().ToArray())).CopyToDataTable()

=> Write Range Workbook transposedDataTable.

Hope it helps!!

[srinivasmarneni (Srinivas M)]

To convert required columns into rows in UiPath, you can use the “Transpose” method. However, UiPath does not have a built-in activity for transposing a DataTable, so you would have to do it programmatically using an Invoke Code activity or by iterating through the data. Here’s a general approach:

1. Read Range*: Use the Read Range activity to read the data from Excel into a DataTable (let’s call it dtInput).
   2.Invoke Code*: Use an Invoke Code activity with the following code to transpose the data:

Dim dtOutput As New DataTable

’ Add columns to dtOutput
dtOutput.Columns.Add(“Name”)
dtOutput.Columns.Add(“Place”)
dtOutput.Columns.Add(“State”)
dtOutput.Columns.Add(“Sports”)

For Each col As DataColumn In dtInput.Columns
If dtInput.Rows(0)(col.ColumnName) IsNot Nothing AndAlso Not String.IsNullOrEmpty(dtInput.Rows(0)(col.ColumnName).ToString()) Then
Dim newRow As DataRow = dtOutput.NewRow()
newRow(“Name”) = col.ColumnName
newRow(“Place”) = dtInput.Rows(0)(col.ColumnName).ToString()
’ Assuming that the next two columns are always Place and State in sequence
newRow(“State”) = dtInput.Rows(1)(col.ColumnName).ToString()
newRow(“Sports”) = dtInput.Rows(2)(col.ColumnName).ToString()
dtOutput.Rows.Add(newRow)
End If
Next

’ Now dtOutput has the transposed data

Make sure to pass dtInput as an In argument and dtOutput as an Out argument to the Invoke Code activity.

3. Write Range*: Use the Write Range activity to write the dtOutput DataTable back to Excel.

Please note that the above code assumes the structure of your data is consistent and that the columns “Name”, “Place”, “State”, and “Sports” are in the same row order in your input data. Adjust the code to fit the actual structure of your data.