 Read excel sheet and take only 10 digits length value

Hello Team,

I am reading excel sheet and excel row data length can be more than 10 digits in any columns. if digits length is more than 10 in length than take only 10 digits from starting of the data . Please help me to provide linq query to resolve that issue.

KEFC.xlsx (7.8 KB)

Hi,

Dim d As Decimal
Dim e As Int32
dt.AsEnumerable.ToList.ForEach(Sub(r)
dt.Columns.Cast(Of DataColumn).ToList.ForEach( Sub(c)
If (Decimal.TryParse(r(c).ToString,d) AndAlso d<>0) Then
e =CInt(Math.Floor(Math.Log10(Math.Abs(d))))
r(c)=Math.Round(d/(Math.Pow(10,e)),9,MidpointRounding.AwayFromZero)*Math.Pow(10,e)
End If
End Sub
)
End Sub
)

Sample20211224-1.zip (7.8 KB)

Regards,

1 Like

Hello Yoichi,

Thank you very much for your help. Result is looks very perfect but the issue is BOT also removing the digits if it is not more than 10 digits in length. Please check the screenshot with BOT text result and manual text result. So you can know more about it.

Sample20211224-1.zip (8.7 KB)

Dim d As Decimal
Dim e As Int32
dt.AsEnumerable.ToList.ForEach(Sub(r)
dt.Columns.Cast(Of DataColumn).ToList.ForEach( Sub(c)
If (Decimal.TryParse(r(c).ToString,d) AndAlso d<>0 AndAlso d.ToString.Length>10) Then
e =CInt(Math.Floor(Math.Log10(Math.Abs(d))))
r(c)=Math.Round(d/(Math.Pow(10,e)),9,MidpointRounding.AwayFromZero)*Math.Pow(10,e)
End If
End Sub
)
End Sub
)

1 Like

Hi,

It seems you found solution for the matter. It might be also effective to check if the string starts with “0” and is integer.

Regards,

I added the condition for that and it’s working fine. Thank you very much .

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