SELECT with string to integer/decimal PARSE is not working

Help
#1

This issue is somewhat covered in some earlier topics, but I still fail to make this work. I have extracted numerical data from web in string format (“123,45”) into a data table. I am using select and parse to filter rows based on the numerical value of the column “c2”, but fail to get it right.

tableName.Select(“int16.Parse([c2], NumberStyles.AllowDecimalPoint) <200”).CopyToDataTable()

is not working. I have tried different version of using ’ and " in SELECT but w/o success. Can anyone help me with this?

Related question. Is it possible to extract data from web and/or excel in numerical format into a data table or do I always need to do a type conversion from string to integer/decimal at some point in data table?

Filter Data Table Activity
#2

Hi @RPANovice,

Try this code
tableName.Select("Convert([c2],System.ToInt32))<200").CopyToDataTable()

Regards,
Arivu

1 Like
#3

Tnx but it is not working.
I have tried this with “System.ToInt32” and “System.Int32” but it is not working. I think problem is the ‘,’ in numerical string (“123,45”) instead of ‘.’. Therefore I now try to use PARSE, but w/o success so far…

#4

Try using filter Datatable activity.

#5

I usually use the Where method using it as an enumerable.
It would look like this and uses you basic vb syntax:

tableName.AsEnumerable.Where(Function(r) Convert.ToDouble(r("c2").ToString.Trim) < 200 ).ToArray

You can switch out ToArray with CopyToDataTable, but ToArray is normally recommended when processing items where you want to keep the entire data set together, whereas converting the filtered set to a new table removes the rest of the data that wasn’t filtered.

The example I showed converted the number to a double which has decimals or use ToInt32 or ToLong for integers ( also CDbl for doubles, or CInt() or CLng() for integers work )

To go further, you will want to make sure the value is a number before you convert it. So using an in-line If condition, it would look like this:

tableName.AsEnumerable.Where(Function(r) If(IsNumeric(r("c2").ToString.Trim), Convert.ToDouble(r("c2").ToString.Trim) < 200, False) ).ToArray

I hope this other method helps or gives you an alternate way of manipulating your data set.

Regards.

#6

Hi Clayton. Thanks for taking precious time to answer. I am new to vb as well, so your answer brought me to new depths of programming. As such IEnumerables (is spelling right?) seems to be good choice for quite a many things, but I do would like to understand why my attempt to PARSE is not working. When numerical value in string format is like “123,56” with a comma, I assume I need to parse numerical string into number with PARSE but my syntax is not working. I’ve tried quite many different syntax variations with ’ and ".

I have worked this for 2 days now. Starting from using “filter data table” to “select” with “convert” and including “replace” with “convert” and then moved to “parse”. So far w/o success. But I will get there - some day, bruh…

#7

Hi, to my understanding “filter data table” cannot be used to filter numerical values stored in string format in data table - like “value < 200”. To my understanding extract from web page or excel always gives you a data table with string values only.

I tried to edit xml code in data extract wizard to turn string column into decimal column, but w/o success. I don’t know if that even is possible. Or maybe that is not recommendable and you should do content validation for the extracted data in your app.

So, I’ve tried quite a few things already. Very good learning experience, but a bit frustrating :anguished:

#8

Hi Clayton,
Now I understand that SELECT does not support PARSE, but it supports CONVERT. With some fine tuning of your example to

table.AsEnumerable.Where(Function(r) Convert.ToDouble(r("c2").ToString.Replace(",", ".").Trim) < 200).CopyToDataTable()

I get response

image

So I’m getting there. Thank you all for your responses.

1 Like
#9

That should work. You can also use other methods to change the comma but Replace would work too.

“Enumerable” is basically another word for “something that can be counted” and is used like a list or array. So when you do table.AsEnumerable you are essentially changing the table to a list of DataRows. ‘Function(r)’ is basically just saying to declare a variable ‘r’ to be used only within the parentheses of that function. ‘r’ can be any character or word, for example you could also use ‘row’; but just make sure that the variable used can identify what it is representing like ‘r’ for row… and since it can only be used within the parentheses, you can use the same variable in the same line too like .Where(Function(r) ).Select(Function(r) ) and so on, if you choose. Lastly, don’t use a variable name that will be conflicted with another existing variable name. For example, if you have a ‘For each row In table’ and you use ‘Function(row)’ in some code then there would be a conflict since ‘row’ is an existing variable representing the DataRow in the For each so should not be used in .Where or .Select code.

Also, this type of expression is called a Lambda expression and there are many resources online to find out how all this all works and with examples. C# code is similar but instead of ‘Function(r)’ you will see ‘r =>’. You will also find that there are resources on LINQ expressions which are closer to something you would see in SQL queries. However, I consider LINQ and Lambda as the same because they offer the same benefits but just do it slightly differently.
For example, here is a LINQ version of the .Where() method:

(From r in table.AsEnumerable Where Convert.ToDouble(r("c2").ToString.Replace(",", ".").Trim) < 200 Select r).CopyToDataTable

So you might see something like that as well, which is basically the same. However, I prefer the other way and believe it’s easier to read or understand.

That is just some useful knowledge to add from a newbie to another newbie.
Anyway, let us know if you need further help.

It looks like from your response, it was working correctly since 82.23 and 150 are < 200. If you need to “change” the values in the table, you will need loop through each row and assign the adjustment. So, the .Where() is just being used to filter and compare values, but not to actually change the values.

Regards.

2 Likes
#10

Thanks Clayton, I really appreciate your use of time to help me. I think I got it figured out now and I learned quite a bit during these two days. I also dug quite deep into Enumerables as well today.

So thanks once more.

#11

How to convert each value of a column in a data table from ‘string’ data type to ‘double’ data type using LINQ?

#12

I’m not sure what you are trying to solve. Is it the green tick mark when you open Excel that often is a problem?

Ideally, you will do this as you perform the process over each row so it takes no additional time. Here are two solutions to fix it per row:

  1. If DataTable already has type Object columns, then simply in an Assign, set the value as a Double type. ie Assign: row(Amount) = Convert.ToDouble(row(Amount).ToString.Trim)

It also makes sense to use an IF activity prior in this scenario, in case the amount is not a number

or

  1. Assign a clone of the data for the schema, then Add Data Row with each value in the desired type for the row. Then, Append the new table of one row to the range, ie Append Range activity.
    image

There’s not really a good way to change all the types without doing it value to value, ie a Loop… as far as I know.

I hope you find this useful. Regards.