Unable to fetch data from excel when having value is 0

Hi All,

I using query to get value from excel and this query is working fine. When it has data like “$2345.44”, but this query get failed when there is no data like “0”. and not having currency symbol with.

DT.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
Dummy Data.xlsx (12.1 KB)

suggestion would be helpful…

Hi @shuklarchana001 ,

Its always better to split the query into steps so that we can validate whether it meets a a given criteria before proceeding to operate on the data that is supposed to be present.

I’ve developed a short workflow, could you give it a try?

To retrieve Row Index of Total Payment:

dt_sampleData.AsEnumerable().ToList().FindIndex(Function(i) i.ItemArray.Select(Function(s) s.ToString.ToLower.Trim).Contains("total payments:"))

To retrieve value of Payment whether it contains dollar sign, zero, or is null(will return zero in case of null) →

dt_sampleData.Rows(int_rowIndex).ItemArray.
	Where(Function(w) Double.TryParse(Regex.Match(w.ToString,"[\d\.]+").Value,0)).
	Select(Function(s) Convert.ToDouble(Regex.Match(s.ToString,"[\d\.]+").Value)).
	FirstOrDefault()

RetrieveTotalPayment.xaml (6.4 KB)

Kind Regards,
Ashwin A.K

Hi @ashwin.ashok Thank you for your reply.

I have tried your code and it is working on total payment . Same I need " Total Amount Owed" and “Balance Remaining” value. So I did replace “Total Payment” with “Total Amount Owed” but it got failed.

Could you please help me to understand it get failed when replace only some other value.
Please see reference:-

Hi @shuklarchana001 ,

Could you give this a try?

RetrieveTotalPayment_v1.xaml (8.4 KB)

Kind Regards,
Ashwin A.K

@ashwin.ashok

Do I need to create variable for Regex? because it is showing Regex is not declared

Hi @shuklarchana001 ,

Sorry my bad, I forgot to mention that you have to import this namespace →

image

Just head over to the Import tab and type in System.Text.RegularExpressions

Kind Regards,
Ashwin A.K

1 Like

Sorry I forget to import package

@ashwin.ashok It is working. Thank you

Hi @ashwin.ashok ,

I need your help.
Actually When I trying to fetch data from balance remaining it will return date value like 3 it is return.
Infact I did add current date format so that it should contain like a row but it is not reading value against balance remaining

dt_sampleData.AsEnumerable().ToList().FindIndex(Function(i) i.ItemArray.Select(Function(s) s.ToString.ToLower.Trim).Contains(“balance remaining as of”+System.DateTime.Now.ToString(“MM/dd/yyyy”)+":"))

Hi @shuklarchana001 ,

It works fine on my end.

image

image

Reason it doesn’t work on your end is because you haven’t added a space after the of

This is what your code produces :

Balance remaining as of03/25/2022

I have specifically used a Contains Method so it isn’t necessary to reference the date.
Don’t edit the LINQ, pass the items into an Array.

Kind Regards,
Ashwin A.K

Got your point.

It is working,

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