My question is if someone could give me a hand with a process I am creating. The thing is that my automation collects a series of fields and reflects them in an excel file as they are coming to a web sequentially.
The problem in question is that many rows in the excel belong to different Salesforce cases (cells A2 and A3), but have the same ID (cells B2 and B3, C2 and C3, D2 and D3). My idea is to try to merge those rows that have the same ID in a single row, so that all the info collected in columns F and G are only in one row, as several cells of these columns are usually empty.
To merge rows with the same ID in a single row in an Excel file using UiPath, you can follow these steps:
Use the Read Range activity to read the Excel file into a data table.
Use a For Each loop to iterate through the rows of the data table.
Inside the loop, use an If condition to check if the current row has the same ID as the previous row. If it does, then merge the rows by concatenating the values in columns F and G of the current row with the corresponding values in the previous row.
If the current row does not have the same ID as the previous row, then do nothing and move on to the next row.
After the loop is complete, use the Write Range activity to write the modified data table back to the Excel file.
Cool, but the photo i posted before its very simplificated. The ID could have an exact match in rows much further in the excel (for example in the row 431)… Any idea?
You can use group by statement and linq to group everything together and merge required columns as well. Here is a sample
(From d In DtBuild.AsEnumerable()
Group d By k=d("Row 1").toString.Trim, k1=d("Row 2").toString.Trim Into grp = Group
Let jn = String.Join(",",grp.Select(function(c) c("ColB").ToString))
Let ra = New Object(){k,k1,jn}
Select r = DtClone.Rows.Add(ra)).CopyToDataTable()
Here dtbuild is the input datatable with all the columns… this groups by Columns ‘Row 1’ and ‘Row 2’ (you can group by any number of columns,just use the same format)…Column with name ‘ColB’ is joined with comma for each group identified…and a new datatable is created with each geoup and the joined columns as one row
Yoichi
ashokkarale
jast1631
Anil_G
adiijaiin
Eric_Alvarado
mukeshkala
Daniel_Rabin1
Avala_Parameswara_Reddy
natcmll