GUIDTEST.xlsx (613.2 KB)
ReplaceBlanksWithGUID.zip (3.0 KB)
Hi All,
I have a requirement to inspect each cell in an excel sheet.
If any cell is empty, a GUID should be inserted.
The column contains >60,000 cells and takes forever to execute (I don’t actually know how long it takes as I’ve never let it complete).
Please could someone help me with a faster / more efficient way of doing this?
I’ll attach the automation and a sample spreadsheet.
Many thanks in advance.
lakshman
(Ganta lakshman)
September 3, 2021, 3:25pm
2
@shaun.mcdowall
Welcome back to our UiPath community.
Try below expression.
newDT = yourInputDT.Clone
newDT = (From r In yourInputDT.AsEnumerable let ra = r.ItemArray.Select(Function (x) x.ToString.Trim.Replace("",“GUID”)).toArray() Select newDT.Rows.Add(ra)).CopyToDataTable()
@lakshman - Thank-you! Where do I insert that expression? Sorry if that’s a silly question!
@lakshman - Many thanks for the explanation; I’ll try that now…
Hi @lakshman
That’s very fast
It doesn’t work though, because the Cells in question are completely empty (NULL value) rather than having the whitespace character in them…
Many thanks,
lakshman
(Ganta lakshman)
September 3, 2021, 3:52pm
7
@shaun.mcdowall
Try replace something like below in above expression and check it once.
Replace("","GUID")
Replace(String.Empty,"GUID")
Hi @shaun.mcdowall
Another way to achieve this by single line of code in invoke code activity
Read the excel and store in dt1
Now use the invoke code activity by setting the argument as dt1 with direction In/Out
Now use the below code
dt1.AsEnumerable().ToList().ForEach(Sub(row) row.ItemArray= row.ItemArray.Select(Function(e)
If(e.ToString.Trim.Equals(""),"GUID",e.ToString)).ToArray())
Regards,
Nived N
Happy Automation