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.
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?
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…
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:
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…
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
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.