How to copy column data from 1st excel to specific column in diffrerent excel using LINQ

Help Studio
, ,
1

How to copy column data from 1st excel to specific column in diffrerent excel using LINQ .

  1. copy data from column ‘Trans Amount’ of 1stTest11591.xlsx and paste in Trans Amount2 for 2nd excel od 2ndTest11591.xlsx
    Its working when I am using for each loop , but its taking very long .
    Please suggest me using LINQ

attaching files:
1stTest11591.xlsx (30.2 KB)
2ndTest11591.xlsx (8.3 KB)

2

@Syed_Sultan_Ahmed1

You can try as below:

Assuming dtSource contains the data from “1stTest11591.xlsx”
and dtTarget is initialized and has the “Trans Amount2” column.

Using LINQ to project and fill the target DataTable
dtTarget = New DataTable() ’ Initialize destination DataTable
dtTarget.Columns.Add(“Trans Amount2”, GetType(Decimal)) Add the target column

Using LINQ to execute the transformation and insertion
dtTarget = (From amount In dtSource.AsEnumerable()
Select dtTarget.Rows.Add(amount.Field(Of Decimal)(“Trans Amount”))).CopyToDataTable()

3

Can you please make workflow, as its not working

4

I have a few queries before attempting this one:

  • The second file has a sheet with unusually high name “Sheet23”; would this file be empty before the copy process?
  • If there is data already in File 2, please upload a sample
  • Is there a matching key (or keys) between file 1 & 2?
  • Why even have File 1, if you can get all information in File 2 from the source?
5

[quote=“sudster, post:4, topic:770147”]

  • The second file has a sheet with unusually high name “Sheet23”; would this file be empty before the copy process?: NO it will have predefined headers and need to add data from column from 1st file and paste it herestrong text
  • If there is data already in File 2, please upload a sample: NO DATA , it will be empty
  • Is there a matching key (or keys) between file 1 & 2?: NO unique(copy data from column name from 1st file and add it on column Letter on 2nd file.
  • Why even have File 1, if you can get all information in File 2 from the source?
6

From what you’ve mentioned, I get the feeling that you are overthinking.

So basically:

  • Load file 1 into a data table
  • Remove unwanted data columns, except the “Trans Amount” column
  • Rename “Trans Amount” column to “Trans Amount2”
  • Add new data columns required for File 2
  • Export to Excel as file 2

Did I miss anything?

7

@Syed_Sultan_Ahmed1

try this:

Certainly! I can outline a complete UiPath workflow that effectively copies the “Trans Amount” column from 1stTest11591.xlsx and pastes it into the “Trans Amount2” column of 2ndTest11591.xlsx using LINQ and UiPath activities. Unfortunately, I can’t create the UiPath XAML file directly, but I can guide you step-by-step on how to set it up.

Step-by-Step Workflow in UiPath

  • Open UiPath Studio and create a new sequence.

  • Drag the Excel Application Scope activity into your workflow.

  • In the properties, set the WorkbookPath to the path of 1stTest11591.xlsx.

  • Inside the Excel Application Scope, add a Read Range activity.

    • Set SheetName to where your “Trans Amount” column is located (e.g., “Sheet1”).
    • Set Output to dtSource (of type DataTable).

  • Drag another Excel Application Scope below the first one.

  • Set the WorkbookPath to the path of 2ndTest11591.xlsx.

  • Inside this scope, add another Read Range activity.

    • Set SheetName where “Trans Amount2” column will be located.
    • Set Output to dtTarget (of type DataTable).

  • Make sure that the dtTarget DataTable has a column named “Trans Amount2”. If it’s not present, you can add it dynamically or verify its presence.

  • You can use an Assign activity to add the column if it does not exist:

    If Not dtTarget.Columns.Contains("Trans Amount2") Then
    dtTarget.Columns.Add("Trans Amount2", GetType(Decimal))
End If

Add an Assign activity that uses LINQ. Since direct manipulation like Add cannot be used with LINQ in a single step, you’ll iterate through the LINQ result:

For Each amount In dtSource.AsEnumerable().Select(Function(row) row.Field(Of Decimal)("Trans Amount"))
    dtTarget.Rows.Add()
    dtTarget.Rows(dtTarget.Rows.Count - 1)("Trans Amount2") = amount
Next

  • After the data has been added to the dtTarget, drag another Excel Application Scope activity to write the data back.

  • Set the WorkbookPath to 2ndTest11591.xlsx.

  • Inside the Excel Application Scope, add a Write Range activity.

    • Set the SheetName to where you want to write the data.
    • Set the Input to dtTarget.
    • Set Range to the starting cell (e.g., “A1” or wherever suitable).

  • Save your workflow and run it. Make sure you have all the necessary file paths set properly and that both Excel files are accessible.

8

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