Merge column 2 column to one column?

Help Activities
, ,
1

I want to merge column APFNME and APLNME ( add space between value column APFNME and value from column APLNME ) ----> Edit column name to Name

Input
image

Output
image

Please guide me about it.
remark : datarow have 100K rows

2

Hi @fairymemay ,

First, declare another DataTable using the Build Activity with the schema you are interested in, then assign this into it → 

(From row In Dt.AsEnumerable()
Let ra = new object() {row(0),row(1).ToString+" "+ row(2).ToString,row(3)}
Select Dt_result.Rows.Add(ra)).CopyToDataTable()

Kind Regards,
Ashwin A.K

3

@fairymemay

Try below steps to do this.

  1. Use Build DataTable activity to create new DataTable with 4 columns like No, Name, Amount, Remark.

  2. Use Read Range activity to read the Data from input file and will give output as DataTable. Let’s say dtInput.

  3. And then use ForEach Row activity and inside use Add DataRow activity and pass ArrayRow Values as below.

    {row("No").ToString,row("APFNME").ToString+" "+row("APLNME").ToString,row("Amount").ToString,row("Remark").ToString}

4

In real data have 97 column.

image
image1889Ă—54 5.61 KB

@lakshman Do you have solution that I don’t create new DataTable ?

@ashwin.ashok Do you have solution that use column name instead index column (I think if future have change number column —> I don’t want edit code)?

5

we would add a new column - Column Name: Name

Assign Activity:
dtData.Columns("Name").Expression = "[APFNME] + ' ' + [APLNME]"

later we can decide if e.g. remove the other columns, reorder cols…

with this approach we have done it for all rows without LINQ, For Each…

1 Like
6

@ppr What activity for add new column?

7

Assign Activity

8

@fairymemay

Use Insert Rows/Column activity and add new column before Amount column. And then use For Each Row activity or Linq query to add those 2 columns. And then finally delete those 2 columns from the DataTable.

9

@ppr right?

image
image845Ă—396 14.1 KB

10

@lakshman Can you sample flow to me?

11

dtData = the name of your datatable variable name, configured at read range

Also we told:

so use an add data column before configuring its column expression

Similar to but with the need to add the data column name before:

Section: 1.3 Data Column Expression Approach

12

@ppr Use for each row right?
because it error.

image
image734Ă—667 24.4 KB

13

no as mentioned above

14

@ppr I don’t clear.

Can you create sample flow to me?

15

yes give me some little time, maybe expression needs a kicker

1 Like
16

@fairymemay Kindly go through the video, this will help you definitely

Once both the column get combines you can use the below method to get the required columns only

dt.AsDataView().ToTable(False,“col1”,“col2”,“col3”)

17

grafik

Add Columns
Configure Expression
grafik

ignore the %, is a visual bug as we have configured:
grafik

then datatable will get changed to
grafik

as you can see without for each, LINQ…

find starter help here.
DCExpression_Concat2Cols.xaml (7.5 KB)

18

@ppr If​ I​ want​ set column​ merge​ in​ position​ column​ C.

How to set​ it?

19

invoke code and using set ordinal method

find starter help:
SetOrdinal.xaml (6.8 KB)

1 Like
20

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