Removing Strings from DataTable and putting Numeric all in one column

Hi Folks,

The situation I have is concerning a Flow I have created where I have had to use Screen Scraping due to the Data being unstructured.
I have then copied this data to a Data Table but the issue I am facing is as follows:
This is my data:

Columns D to I all contain the qty, but it is inconsistent as to which column it stays in.
Columns D to I also contain other info in text format.

Question 1
Is it possible to remove anything that is a string, irrespective as to whether it has dashes, hashtags or just text. All strings.

Question 2
Is it possible to put the numeric character (there will only ever be 1) in one consistent column?

Desired Output:
image

Sample Data2.xlsx (9.0 KB)

Hi,

How about the following?

img20220518-1

dtResult = dt.AsEnumerable.Select(Function(r) dtResult.LoadDataRow(r.ItemArray.Take(3).Concat({r.ItemArray.Skip(3).Max(Function(v) if(IsNumeric(v.ToString),CInt(v.ToString),0))}).ToArray,False)).CopyToDataTable

Sample20220518-1a.zip (21.8 KB)

Regards,

1 Like

That works exactly how I want it to - just need to apply it to my Flow now.

Thank you ever so much!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.