Read excel sheet and take only 10 digits length value

[ALAM_MD]

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.

Thanks in advance.

KEFC.xlsx (7.8 KB)

[Yoichi]

Hi,

How about the following sample?

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,

[ALAM_MD]

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.
Please help to resolve that issue.

Uploading: BOT & Manual-2.PNG…
Sample20211224-1.zip (8.7 KB)

[ALAM_MD]

[ALAM_MD]

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
)

[Yoichi]

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,

[ALAM_MD]

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

.