Very Slow For Each Row in Data Table

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.

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

@shaun.mcdowall

  1. First use Read Range activity to read the data from excel file and will give output as DataTable. Let’s say ‘yourInputDT’.

  2. Then write above expression in Assign activities.

  3. Then use Write Range activity and pass newDT and sheet name to write it back to same excel file.

@lakshman - Many thanks for the explanation; I’ll try that now…

Hi @lakshman

That’s very fast :slight_smile:

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,

@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