0

I have an account number in excel (.csv) which I read into a dataTable. The account number is displayed as 5.01E+13 in excel and hence reads the same into a dataTable. Whereas the original value is 50100393946569. I need to compare the account numbers from Excel with the ones stored in the database. How to bring 5.01E+13 to its original format 50100393946569 either in VB or SQL

Below is code of reading excel to datatable

Dim sreader as StreamReader
Dim sstring as String
Dim dt as DataTable
Sreader = File.OpenText(Path.ToString) 'this path is path of the excel


While sreader.Peek <> -1
sstring = sreader.Readline

Dim str as String ()  = sstring.split(",")


Dim rowdt as DataRow
rowdt = dt.NewRow()


For i as Integer = 0 to dt.Columns.count-1

rowdt(i) = str(i).ToString
Next
dt.rows.add(rowdt)


End while
Muriel Mi
  • 21
  • 5
  • Yeah, this is one of these irritating helpful features of Excel. If you've saved in Excel the spreadsheet it has quite possibly overwritten the original value with the scientific notation and you may not be able to retrieve it. There are heaps of posts on heaps of different forums about this type of thing, comes down to specifying the correct formats in Excel before saving, Search Excel & barcodes where it does the same thing – Hursey Nov 09 '21 at 02:18
  • @Hursey can u provide me any links for reference. – Muriel Mi Nov 09 '21 at 03:00
  • https://www.excelforum.com/excel-general/673919-entering-a-barcode-without-getting-12-formatted-in.html – Hursey Nov 09 '21 at 03:01
  • So code level there is no solution to this? Modifications need to be done in Excel itself? – Muriel Mi Nov 09 '21 at 04:28
  • If the number is shown as 5.01E+13 if you open that csv file in notepad, then you're out of luck. – F0r3v3r-A-N00b Nov 09 '21 at 05:40
  • Does this help: https://stackoverflow.com/questions/22190630/how-to-convert-the-display-format-scientific-notation-to-double or https://social.msdn.microsoft.com/Forums/en-US/2f60a87c-a7a8-4d6a-8ce4-86a1c09bf159/convert-scientific-notation-to-double – Jon Roberts Nov 09 '21 at 10:26

0 Answers0