How to concat common rows in excel file and and write into single cell

How to concat excel file and and write into single cell

Hi, Sreekar! Can you describe the task in more detail?

Maybe this would help:

Hi

Hope the below steps would help you resolve this

  1. Let’s take you have the datatable named dt

  2. Now use a OUTPUT DATATABLE activity and pass dt as input and get the output as string named strtable

  3. Then use a Write cell activity and pass that string as input

  4. This will type the entire datatable in a cell

If you want to remove the duplicates in that table then use REMOVE DUPLICATE RECORDS activity and pass dt as input and get same dt as output

Cheers @Sreekar_Mulinti

for example i want to write excel from first file to second file


Hi @Sreekar_Mulinti,

Use a assign activity in that do a clone of your original Dt for example your original DT is dt_TransactionData and the clone is out_Dt.

then take a assign activity again and in the LHS pass out_Dt and in RHS the below query

(From row In dt_TransactionData
Group row By a=row(“Date”).toString, b=row(“City”).toString Into grp=Group
Where grp.count>1
Let c= String.Join(“,”,grp.AsEnumerable().Select(Function(arg As Datarow) arg(“Numbers”).ToString))
Select out_Dt.Rows.Add(a,b,c)
).CopyToDataTable

print the out_Dt in the file you want the result.

Let me know for any queries

Thanks!!

how to concat multiple rows just like numbers row?

Use the query provided above

Thanks!

This case we would do with grouping the data

prepare a target datatable - dtTarget
Assign Acitvity
LHS: dtTarget | datatype: DataTable
RHS: YourOriginDataTable.Clone

Assign Activity:
LHS: dtTarget
RHS:

(From d In YourOriginDataTable.AsEnumerable
Group d By k1=d(“Date”).toString.Trim, k2=d(“City”).toString.Trim Into grp=Group
Let nj= String.Join(",", grp.Select(Function (n) n(“Numbers”).toString.Trim))
Let ra = New Object(){k1,k2, nj}
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

Another method to solve the issue.

  1. Read files to separate data tables.

  2. Than use Merge Data Table activity.

  1. Use remove Remove Duplicate Rows (optional).
  1. Write data table to a new Excel file.

how about scinerios like this:
from excel 1 to excel 2


Try using this activity

Try this -

(From row In dt_TransactionData
Group row By a=row(“Date”).toString, b=row(“City”).toString Into grp=Group
Where grp.count>1
Let c= String.Join(" “,grp.AsEnumerable().Select(Function(arg As Datarow) arg(“Numbers”).ToString))
Let d= String.Join(” ",grp.AsEnumerable().Select(Function(arg As Datarow) arg(“description”).ToString))
Select out_Dt.Rows.Add(a,b,c,d)
).CopyToDataTable

Thanks!!

similar to above we would extend:

(From d In YourOriginDataTable.AsEnumerable
Group d By k1=d(“Date”).toString.Trim, k2=d(“City”).toString.Trim Into grp=Group
Let nj= String.Join(" ", grp.Select(Function (n) n("numbers").toString.Trim))
Let dj= String.Join(" ", grp.Select(Function (n) n("description").toString.Trim))
Let ra = New Object(){k1,k2, nj, dj}
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

Assign: The source contains no DataRows.

Try this -
(From row In dt_TransactionData
Group row By a=row(“Date”).toString, b=row(“City”).toString Into grp=Group
Let c= String.Join(" “,grp.AsEnumerable().Select(Function(arg As Datarow) arg(“Numbers”).ToString))
Let d= String.Join(” ",grp.AsEnumerable().Select(Function(arg As Datarow) arg(“description”).ToString))
Select out_Dt.Rows.Add(a,b,c,d)
).CopyToDataTable

Assign: Object reference not set to an instance of an object.

have you done the clone of your original DT in the resulting DT?

ive done the clone but the data is not concating

the output remains same not concating