Copy pasting data from column to each new row

Irfan_Musa · June 25, 2024, 9:45am

Hi everyone

I am trying to copy paste from one data table to another.

I already created a new row as below. But I am not sure how to put data from different column together to a new one under 1 new column of the Value to Save.

I have a dummy data below to demonstrate the file that I read and the Output I want.
I have read range below data:

My desired Output:

I know for a single value to save I can just write the variable there.
For NewRow(“NOx”) = A1
But currently I have A1,A2,A3 from different column in the first datatable that I read.

Is there a method to combine data from different column to only one new column?
For example NewRow(“NOx”) = A1,A2,A3

vrdabberu · June 25, 2024, 10:14am

Hi @Irfan_Musa

=> Read Range Workbook
Output → dt

=> Use below code in Invoke Code:

' Initialize the new DataTable
newDt  = New DataTable()
newDt.Columns.Add("AA", GetType(String))
newDt.Columns.Add("BB", GetType(Integer))
newDt.Columns.Add("CC", GetType(Integer))
 
' Add the first set of columns to the new DataTable
Dim transformedRows1 = dt.AsEnumerable().Select(Function(row) New With {
    .AA = row.Field(Of String)("M1"),
    .BB = row.Field(Of Double)("M1.1"),
    .CC = row.Field(Of Double)("M1.3")
}).ToList()
 
' Add the second set of columns to the new DataTable
Dim transformedRows2 = dt.AsEnumerable().Select(Function(row) New With {
    .AA = row.Field(Of String)("M2"),
    .BB = row.Field(Of Double)("M2.1"),
    .CC = row.Field(Of Double)("M2.3")
}).ToList()
 
' Add the transformed rows to the new DataTable
For Each row In transformedRows1
    newDt.Rows.Add(row.AA, row.BB, row.CC)
Next
 
For Each row In transformedRows2
    newDt.Rows.Add(row.AA, row.BB, row.CC)
Next

Invoked Arguments:

=> Write Range Workbook newDt back to excel.

Check the below workflow:

Sequence18.xaml (8.9 KB)
Input.xlsx (8.7 KB)

Output:

Regards

Irfan_Musa · June 26, 2024, 9:01am

Hi @vrdabberu,

I tried the invoke code method but I am currently facing this error regarding the data type:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidCastException: Unable to cast object of type ‘System.Double’ to type ‘System.String’. at System.Data.DataRowExtensions.UnboxT1.NonNullableField(Object value) at UiPathCodeRunner_6bf1a3c0dc0242a9abdc28ac18bb389e._Closure$__._Lambda$__0-0(DataRow row) at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at UiPathCodeRunner_6bf1a3c0dc0242a9abdc28ac18bb389e.Run(DataTable dt, DataTable& newDT) at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor) at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span1 copyOfArgs, BindingFlags invokeAttr)
— End of inner exception stack trace —
at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span1 copyOfArgs, BindingFlags invokeAttr) at System.Reflection.MethodBaseInvoker.InvokeWithFewArgs(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args) at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object[] args) at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs, IEnumerable`1 imps, Object args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Below is the data from excel file that I read and save as datatable. I want to read from row 10 as the header is unique to get the any data below the column :

I try changing the argument as below (This is my first time using invoke code method actually). I hope I am doing it right.

Below is the output table header (csv file) that I need to write the final output. Which I have match as per your example.

Invoke code I have changed:

’ Initialize the new DataTable
newDt = New DataTable()
newDt.Columns.Add(“Eng_Model”, GetType(String))
newDt.Columns.Add(“NOx”, GetType(String))
newDt.Columns.Add(“Eng_SFOC”, GetType(String))

’ Add the first set of columns to the new DataTable
Dim transformedRows1 = dt.AsEnumerable().Select(Function(row) New With {
.Eng_Model = row.Field(Of String)(“ME1.TYPE”),
.NOx = row.Field(Of String)(“ME1.NOX”),
.Eng_SFOC = row.Field(Of String)(“ME1.SFOC”)
}).ToList()

’ Add the second set of columns to the new DataTable
Dim transformedRows2 = dt.AsEnumerable().Select(Function(row) New With {
.Eng_Model = row.Field(Of String)(“ME2.TYPE”),
.NOx = row.Field(Of String)(“ME2.NOX”),
.Eng_SFOC = row.Field(Of String)(“ME2.SFOC”)
}).ToList()

’ Add the transformed rows to the new DataTable
For Each row In transformedRows1
newDt.Rows.Add(row.Eng_Model, row.NOx, row.Eng_SFOC)
Next

For Each row In transformedRows2
newDt.Rows.Add(row.Eng_Model, row.NOx, row.Eng_SFOC)
Next

The data has decimal point.
Need your advise on this, thank you.

vrdabberu · June 26, 2024, 9:15am

Hi @Irfan_Musa

Please check the code I have provided in the invoke code…i have given the double and you have taken it as string please check that.

Regards

anwarkhan · June 26, 2024, 11:40am

I have used the ‘Build DataTable’ activity to create headers and used two LINQ queries.

Use two Assign activities and keep each query in one.
Store the queries in a List<DataRow> datatype variable:

vr_ls [list(Datarow)] = dt_out.AsEnumerable.Select(function(x) dt_.Rows.Add({ x("M1").ToString , x("M1.1").ToString ,x("M1.3").ToString } )).ToList

The above LINQ query adds data from columns ‘M1’ to ‘M1.2’ into an empty DataTable with headers AA, BB, and CC.

vr_ls [list(Datarow)] = dt_out.AsEnumerable.Select(function(x) dt_.Rows.Add({ x("M2.1"), x("M2.2"),x("M2.3")} )).ToList

The above LINQ query adds data from columns ‘M2.1’ to ‘M2.3’ into a second DataTable.

Now you can use the ‘Write Range’ activity to give input to ‘dt_’.

Irfan_Musa · June 27, 2024, 7:39am

Hi @vrdabberu

It was initally having this error when I first tried it, so I thought of changing everything to string data type:

I changed my code:
’ Initialize the new DataTable
newDt = New DataTable()
newDt.Columns.Add(“Eng_Model”, GetType(String))
newDt.Columns.Add(“NOx”, GetType(Integer))
newDt.Columns.Add(“Eng_SFOC”, GetType(Integer))

’ Add the first set of columns to the new DataTable
Dim transformedRows1 = dt.AsEnumerable().Select(Function(row) New With {
.Eng_Model = row.Field(Of String)(“ME1.TYPE”),
.NOx = row.Field(Of Double)(“ME1.NOX”),
.Eng_SFOC = row.Field(Of Double)(“ME1.SFOC”)
}).ToList()

’ Add the second set of columns to the new DataTable
Dim transformedRows2 = dt.AsEnumerable().Select(Function(row) New With {
.Eng_Model = row.Field(Of String)(“ME2.TYPE”),
.NOx = row.Field(Of Double)(“ME2.NOX”),
.Eng_SFOC = row.Field(Of Double)(“ME2.SFOC”)
}).ToList()

’ Add the transformed rows to the new DataTable
For Each row In transformedRows1
newDt.Rows.Add(row.Eng_Model, row.NOx, row.Eng_SFOC)
Next

For Each row In transformedRows2
newDt.Rows.Add(row.Eng_Model, row.NOx, row.Eng_SFOC)
Next

Irfan_Musa · June 27, 2024, 8:13am

Hi @anwarkhan,

I am currently trying your method. Can I know for the “dt_out” which data table should i use (is it the main data I read initially from the excel?)
and for “dt_” is it just a variable for storing output?

vrdabberu · June 27, 2024, 8:18am

Hi @Irfan_Musa

I have attached the xaml in earlier post please check and let me know whether it;s working or not?

Regards

Irfan_Musa · June 27, 2024, 8:25am

It works on your files, but when I try to implement it using my data it just throws the data type error.

anwarkhan · June 27, 2024, 8:54am

Yes, the input variable ‘dt_out’ contains the data, while ‘dt_’ is an empty table with only headers.

Irfan_Musa · July 3, 2024, 12:10am

Hi @anwarkhan

This solution works, I am gonna try to apply to my other parts of automation.
I am not sure why but the LINQ you provided I have to add “.ToArray” at the end at it works fine for mine.

anwarkhan · July 4, 2024, 5:43am

Hi @Irfan_Musa

Happy to hear that it worked, you can always try these Queries to apply it in different scenarios and do modify accordingly.
Your data type on left hand side must be ‘Array’ so you required it to change output of query in array.

system · July 7, 2024, 5:44am

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
How to Copy paste same value in all rows of same column? Help	3	3418	February 28, 2019
Copy/Paste selected value only into new excel Help excel , activities	1	6038	February 1, 2018
Copy selected rows cell to new columns Studio excel	3	1113	September 19, 2020
How to copy data from only 1 column and paste it onto web form Activities excel	7	824	May 18, 2022
How to paste column by column in excel Marketplace excel , studio , question	7	1011	February 21, 2020

Copy pasting data from column to each new row

Related topics