Removing the first numbers in a string

Hi @Maverick,

Convert the number into string and follow this method.

Substring helpes here…

It will be successful even if you remove comma from the entire number.
image

Thanks,
Jiban

Thanks, this is working like i wanted !!

1 Like

Hi Mahesh,
your solution is more flexible,as it is working with all values. thanks !!
I need to do a summation of this column, containing comma values like… 1,00 2,00 10,00 etc. Comma represents decimal notation so i need to ignore the zeroes and consider only preceeding value. I tried using “Compute(SUM(columnname)” but it won’t work… how can i alter the values in datatable itself and do the summation?

1 Like

@Maverick

Try this

Declare a variable with type double Sum

Sum=dt.AsEnumerable.Sum(Function(row) Convert.ToDouble(row(“Column_Name”).ToString.Split({“,”},stringSplitOptions.RemoveEmptyEntries)(0)))

Regards,
Mahesh

1 Like

Hi mahesh…

I have a data like this 1,234.00 i need to remove the decimal nd 2 zeroes. Can u help m

@Darshhan

Let us take

strA=1,234.00

strB=strA.Split({“.”,StringSplitOptions.RemoveEmptyEntries).ToArray(0).ToString

Regards,
Mahesh

Hi @Darshhan,

This activity will help you. It has with sample

Regards
Balamurugan.S

Hi mahesh…

I scraped a structured data from the web. The problem is that some string in the data contains comma, and when I put the structured data in a csv that’s a problem because I can’t divide it properly with the unwanted comma.

How can I scan over the entire structured data to remove the comma and afterwards write the csv?

Thanks a lot
Stefano

Hi @Stefano_Skjupyter

You can use a simple .Replace method on a string, like this:

yourString.Replace(",","")

This replaces all commas with nothing, effectively removing all commas.

1 Like

Thanks @loginerror

the problem is my data type is a data table, not a single variable type string. I think I should iterate over it with a for cycle for example. is that correct?

Stefano

Indeed, that would be the approach. I am pretty sure someone knows a 1-liner for that in an Assign activity, but they would have to speak up (maybe @ClaytonM, if he feels like it; I don’t like using direct tags like that :slight_smile: )

Hi @Stefano_Skjupyter

From my understanding, you can’t change row items in a datatable without using a For Each to loop through each row.

Also, you don’t necessarily need to remove the comma because if you surround that item with quotes, then the CSV will delimit the value correctly.

So basically if would look like this:

For each activity: For each row in dt1
            If row.Item("columnname").ToString.Contains(",")
                  Assign: row.Item("columnname") = """"+ row.Item("columnname").ToString+""""
                  // or row.Item("columnname") = row.Item("columnname").ToString.Replace(",","")

So that would loop through each row and change the value using an Assign activity.

If the data has thousands of rows, this may not be an efficient solution. The other method would be to manipulate the entire structured data as a string (ie using Output Data Table). However, if you look at the entire row as one string, I don’t know how you would identify which commas are delimiters and which commas need to be removed.

EDIT: added if condition in pseudocode to check if value contains comma

Regards.

Oh, you can also filter your table prior to replacing the commas, rather than looping through each row.

That would be like this:

For Each (Framework) activity: For each row in dt1.AsEnumerable.Where(Function(r) r("columnname").ToString.Contains(",") ).ToArray
         Assign: row("columnname") = """"+ row.Item("columnname").ToString+""""
                  // or row.Item("columnname") = row.Item("columnname").ToString.Replace(",","")

I am used to programming in python where I can put I piece of code essentially where I want to, but in this platform where should I copy paste your piece of code to be executed??

sorry if the question might sound silly

Regards

Sorry let me clarify. My examples were just pseudocode as a representation of the logic.

Here is a more visual representation done in UiPath:
image

Here are the example For Each activities in a workflow:
foreach.xaml (12.9 KB)

1 Like

Ok @ClaytonM thanks for the clarification.
The data has thousands of rows so it may not the efficient solution to loop over the entire dataset. Is there a way to remove the comma as soon as is scraped from the website?

For instance, another web scraper named Parsehub, give the possibility to insert a Regex command when the string is scraped in order to remove the comma or the currency… which is more efficient instead of re-processing all the database afterwards…

Have you got my point?

Regards

If you are using Extract Structured Data activity to scrape the dataset, then as far as I know, you can’t change the values during that step. But I’m not saying it’s not possible either, and have not seen it.

You can also use other script files to process your data which may be faster. However, I can’t say I’m an expert on doing this with DataTables. There are Python, Powershell, and VBA activities in UiPath, which you can use for this.

One solution you might consider is using Excel Write Range to output your data to Excel rather than CSV, then if you prefer it to be CSV, use the Save As dialogue to generate the CSV file. This would remove any need to fix the delimiting because Excel would do it for you.

Regards.

1 Like

Also keep in mind that “thousands of rows” isn’t very much. It would likely take 1-2 seconds max to loop through 999,999 rows if you’re just doing the if statement and replacing the comma. If you’re doing it a few million times then I’d spend more time on making efficient code. Otherwise it’s less efficient trying to optimize every little bit :slight_smile:

1 Like

That is a great advice, using the Excel writer instead csv I overcome all problem related to the comma.

Thanks a lot guys for the support!

Regards

1 Like

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