Hello All,
I need to fetch amount “$310.35” and trying to use below query but it is showing error “NumberStyles is not declared” …
VarDouble=Double.Parse((elephantDT.AsEnumerable().Where(Function(w) w(0).ToString.Contains(“Total Amount Owed:”)).Select(Function(s)s.ItemArray.Where(Function(sw) sw.ToString.IsNumeric).FirstOrDefault).FirstOrDefault()),NumberStyles.Currency, cultureinofUS)
Any suggestions on this???
Add System.Globalization to the namespaces via the import panel (right from variables panel)
About the LINQ we do have some doubts, but will step in later if it is needed
I have added same. but now it’s showing disallow to implicit conversion from object to string
please share with us some sample rows and give us some more details on what you want to achieve.
When using the first found column value which isNumeric then you also risk an invalid value exception for the default case.
FirstOrDefault).FirstOrDefault() ← last FirstOrDefault will return not a string, which is expected for the Double.Parse. This could be the reason for the object / string missmatch.
Also keep this in mind:
So a $123 Value will never be passed to the conversion function
For more Clearity, I m attaching dummy data format.
Dummy Data.xlsx (12.5 KB)
In this sheet, we have Total Amount owed. and need to fetch that amount
Earlier , I did use this query,
varInt=Convert.ToInt32(elephantDT.AsEnumerable().Where(Function(w) w(0).ToString.Contains(“Total Amount Owed:”)).Select(Function(s) s.ItemArray.Where(Function(sw) sw.ToString.IsNumeric).FirstOrDefault).FirstOrDefault())
But this query was giving me only integer like when we have “$302.45”, It will give “303”. So trying to put convert into double and currency,sultureinfous.
Hi,
Your currency sign in the sheet is defined as not value but format style. If we get value from the sheet with default settings, the result is just numeric.
So can you try the following steps?
First, Read data as DataTable using ReadRange activity with PreserveFormat option.
Then use the following expression.
elephantDT.AsEnumerable().Where(Function(w) w(0).ToString.Contains("Total Amount Owed:")).Select(Function(s) s.ItemArray.Where(Function(sw) Double.TryParse(sw.ToString,System.Globalization.NumberStyles.Any,New System.Globalization.CultureInfo("en-US"),New Double)).FirstOrDefault).FirstOrDefault().ToString
If you need just numeric value, the following will work without regard to PreserveFormat
doubleVar = Double.Parse(elephantDT.AsEnumerable().Where(Function(w) w(0).ToString.Contains("Total Amount Owed:")).Select(Function(s) s.ItemArray.Where(Function(sw) Double.TryParse(sw.ToString,System.Globalization.NumberStyles.Any,New System.Globalization.CultureInfo("en-US"),New Double)).FirstOrDefault).FirstOrDefault().ToString,System.Globalization.NumberStyles.Any,New System.Globalization.CultureInfo("en-US"))
Regards,
@shuklarchana001
kindly note: values seen in excel like:
can be different later in the datatable after read range
When we are taking too much constraints e.g. $ character is mandatory, or isDigit fails beacuse of dollar char is present we do risk that less cases we will get the amount as we could get.
For an initial start in order to have a balance of differenct aspects a LINQ could look like this:
(From d In dtData.AsEnumerable
Where d(0).ToString.Contains(“Total Amount Owed:”)
Let jra = String.Join("|", d.ItemArray )
Let mtc = Regex.Match(jra, "(?<=\$)?[\d,.]+" )
Where mtc.Success
Where Double.TryParse(mtc.Value, NumberStyles.Any, New CultureInfo("en-US"), Nothing)
Select x = Double.Parse(mtc.Value, NumberStyles.Any, New CultureInfo("en-US"))).DefaultIfEmpty(-1).First()
filtering only the total Amount rows
optional $ and simple pattern for the amount accepting also , and .
Filtering on regex match success (we had a match)
Filtering Value was parseable
Parsing the value
And if it was failing / not retrieving any value then we do return -1 (or any other default value, Or Null)
Hi @Yoichi ,
Thank you for reply.
I did use with same solution but it is working, when I am using Excel application scope activity and use read activity where we have property “Preserve Format”.
But in my scenario, I have to use Read activity from workbook without excel application scope. where we don’t have property “Preserve Format”.
In this scenario, this won’t work.
Hi @ppr
Thank you for your solution.
I would request, to share this samplw main.xaml file. That would be helpful to see.
Hi,
I think System-File-Workbook-ReadRange also has PreserveFormat and it works as the following.
Regards,
Hi @Yoichi,
I am trying to use same query for “Total Payments:” but it showing object refec error.
elephantDT.AsEnumerable().Where(Function(w) w(0).ToString.Contains(“Total Payments:”)).Select(Function(s) s.ItemArray.Where(Function(sw) Double.TryParse(sw.ToString,System.Globalization.NumberStyles.Any,New System.Globalization.CultureInfo(“en-US”),New Double)).FirstOrDefault).FirstOrDefault().ToString
For Reference sharing screenshot:-
or you can see dummy data xlsx file,which I had already shared earlier.
Can you please suggest??
as mentioned above we would not recommend this LINQ, due it is conflicting with some essential basics.
As also given some alternates and also introducing into the case of excel/resulting datatable values after read range we pointed out, the differences on it.
It looks like the marker for the totals can be varying. Maybe you will in a first-round recheck the documents and redefine the complete requirements.
I would request, to share xaml file, that you had created your query because I was trying to use that but might some issue unable to use that.
Please share your query with file. That would be more helpful
@ppr Thank you for sharing file.
As you shown earlier value. but I need value should come up with currency symbol.
I run this file. This will come up without currency symbol with amount.
for example :- if amount has value like “$345.34” Than this should fetch same like “$345.34”
kindly note:
(?<=\$)?[\d,.]+"
and do use it when it is present
we did also run the code and retrieve the value according to the requirement
in case of you need later a dollar char on the retrieved amount we would recommend to use a toString(…) for reformating the amount double like:
But lets not mixup retrieval and reformating. And as shown within the datatable screenshot. A not present $ char cannot be part of a retrieval result
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
ashokkarale
pravallikapaluri
vrdabberu
mkankatala
Anil_G
naveen.s
dutta.marina
A_Learner
MD_Farhan1
rockchord