2

I am reading the Excel having a column of long numeric values of more than 8 digit. It also may start from 0 for example 003787677. Examples in my case are 173370148, 142350093 etc.

The values in dataset have issue for the columns having more than 8 digit length, it converts them in to exponential value like 1.4235e+008

I don't want this value to be converted to exponential or a way to convert it back to correct value.

I cannot change excel column format to number as i may have values started with 0 in front or 00 in front. So i am using text as column format cell

I am using following type of connection while reading .xls at windows 2003 server machine 32 bit:

provider = "Microsoft.Jet.OLEDB.4.0;Data Source=@filename;Extended Properties='Excel 8.0;HRD=Yes;IMEX=1";

Using below statements to read excel:

OleDbDataAdapter oleAdpt = new OleDbDataAdapter("SELECT * From [Sheet1$]", con);  
oleAdpt.Fill(dtexcel);
pappbence96
  • 1,164
  • 2
  • 12
  • 20
Tipur Madan
  • 23
  • 4
  • So what's the problem here? Please try to describe what exactly you are looking for as a solution. – Mirko Brandt Jul 23 '19 at 06:47
  • The problem is: When it fills the values from excel in datatable, some of the values are converted to exponential values for example 142350092 is converted to 1.4235e+008. The values less than or equal to 8 digit are good – Tipur Madan Jul 23 '19 at 07:03
  • @GSerg : This is not answered in the link you posted. the below code converts it to non exponential but it appends 0's at the end without actual value: "SELECT Format([F1], 'General Number') From [Sheet1$]" -or- "SELECT Format([F1], \"#####\") From [Sheet1$]" And other solutions are not relevant to this question Request you to please remove the tag for "already answered" – Tipur Madan Jul 23 '19 at 10:16
  • @TipurMadan That is the exact duplicate of what you are asking, and it contains several different solutions. You are probably not going to get any better than that. I can remove the binding close vote, but it will only result in other people closing it again for the same reason. – GSerg Jul 23 '19 at 10:19
  • Possible duplicate of [Scientific notation when importing from Excel in .Net](https://stackoverflow.com/q/429853/11683) – GSerg Jul 23 '19 at 10:20
  • Thanks @Gserg : I got one better solution with no code change required. I posted my solution in the same above link which contains 10 answers. :) – Tipur Madan Jul 24 '19 at 04:43
  • You said "*I cannot change excel column format to number as i may have values started with 0 in front or 00 in front*". The General format will get rid of your zeroes too. – GSerg Jul 24 '19 at 06:37
  • yes you are correct, in General format when you want to edit the values by prefixing 0, then it removes those 0's. But first we want to make it text format to add 0's and then if we format it to General, then it retains the 0's unless we manually go to edit the value in the cell. So we will be good :) – Tipur Madan Jul 25 '19 at 04:38

1 Answers1

0

To avoid conversion to exponential, don't directly assign the cell value to a double or integer data type. Use string instead then do conversion in your code.

Michael Malia
  • 1