Find (" ") and replace ("") values in Excel file with 50k+ rows

[jan.kovac]

Hello,
can someone please help me with this process? I´ve got an Excel file that is generated every Monday and I need to process this file via robot and send it via email. This file always contains 50k+ rows and in the first column (“Cust. Material”), there are always values containing spaces that I need to eliminate. I could do this using a for each loop, but unfortunately with that many values, this loop would take ages. When I tried googling for a different solution, I found two options: a flowchart or LINQ query. Since I’m a beginner, I don’t really know how to do it at least one of these ways.
example:
source A2 value: L65 000 035
required value A2: L65000035

Thank you so much

[Anil_G (Anil Gorthi)]

@jan.kovac

Read the data into datatable dt1 then use this in invoke code and writ data back to excel

dt.AsEnumerable.ToList.ForEach(Sub(x) x(0) = x(0).ToString.Replace(" ",""))

Add dt as argument to invoke code with in/out as direction

Cheers

[rodrigo.simao]

Hello @jan.kovac! I think you can assign these results to a variable or just use the “CurrentItem” if you are using a For Each activity, then using the Assign activity you can do something along the lines of “CurrentItem.ToString.Replace(” “,”“)”.

Let me know if this helps, Cheers!!

[Nikhil_Vernekar]

Excel_Data_Trim.zip (10.3 KB)

Attached is the workflow. Let me know if this helps!

[jan.kovac]

Thank you so much, you are all geniuses!