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

[Sreekar_Mulinti]

How to concat excel file and and write into single cell

[Sergey_Petrenko]

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

Maybe this would help:

Remove duplicate rows

Hello all & @ppr I have an issue with features on excel file. I’m trying to remove the duplicate row in a data table. Sample of DT : |First Name|Last name |ID| |Brigite|Tabar|12345| |Frederic|Alban|32415| |Brigite|Tabar|67890| I use this to remove duplicate row : (From d in DT.AsEnumerable() Group d By ld= d(“ID”), into grp=Group Select grp.First()).CopyToDatatable() But with this the bot will remove the first line : |Brigite|Tabar|12345| and i want that the bot rem…

[Palaniyappan (Forum Leader)]

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

[Sreekar_Mulinti]

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

[Shikhar_Tandon]

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!!

[Sreekar_Mulinti]

how to concat multiple rows just like numbers row?

[Shikhar_Tandon]

Use the query provided above

Thanks!

[ppr (Peter Preuss)]

This case we would do with grouping the data

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

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

[Sergey_Petrenko]

Another method to solve the issue.

1. Read files to separate data tables.

2. Than use Merge Data Table activity.

How can I merge two data tables?

I have 2 data tables in which I need to get values from column 1 of the 1st data table and merge it with a second data table. Can anyone please explain to me how to achieve this task.

Activities - Merge Data Table

3. Use remove Remove Duplicate Rows (optional).

Activities - Remove Duplicate Rows

4. Write data table to a new Excel file.

[Sreekar_Mulinti]

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

[Sergey_Petrenko]

Try using this activity

Append Range

[Shikhar_Tandon]

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!!

[ppr (Peter Preuss)]

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

[Sreekar_Mulinti]

Assign: The source contains no DataRows.

[Sreekar_Mulinti]

[Shikhar_Tandon]

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

[Sreekar_Mulinti]

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

[Shikhar_Tandon]

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

[Sreekar_Mulinti]

ive done the clone but the data is not concating

[Sreekar_Mulinti]

the output remains same not concating