Unable to use "convert double.parse(Value,NumberStyles.Currency,CultureInfoUS)"

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.


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"))


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.


I think System-File-Workbook-ReadRange also has PreserveFormat and it works as the following.


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

find starter help here:
ppr_ExcelBox_Shuklarchana001.xaml (7.7 KB)

@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:

  • we introduced to you the difference between visual appearance in excel, but not present in data table
  • in the RegexPattern we do a conditional anchoring on the dollar

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

@ppr and @Yoichi

Thank you for supporting.
THis is working with my earlier query.

1 Like

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