Problem reading big number from Excel file in C#?

Question

_{Thursday, March 21, 2013 1:04 PM}

I am getting data from Excel file through OleDBConnection in a DataTable. It works fine. But there is a column say "CardNo" which have big numbers like one "12345678910111200". when I try to read it from DataTable it displayes "1.234567891e+016". How to get these numbers of this column in original as they displays in excel file.

I tries to change the datatype in DataTable of this particular column then this error occurs:

"Input string was not in a correct format.Couldn't store <1.234567891e+016> in CardNo Column.  Expected type is Int64."

Is there a way to solve this issue?

All replies (13)

_{Thursday, March 21, 2013 1:07 PM ✅Answered}

You can try:

Int64 largeInt = Convert.ToInt64(bigNumber);

_{Friday, March 22, 2013 8:32 AM ✅Answered}

   Int64 d = Convert.ToInt64(1.234567891e+016);
   Console.WriteLine(d );    

This can fix your problem~.

maybe still needed to consider using Biginteger if you need process calculation.Refer to

http://msdn.microsoft.com/en-us/library/system.numerics.biginteger.aspx

_{Friday, March 22, 2013 4:59 AM}

If you're trying to get the big number into an int, the max it can be is 2.147 Billion, so instead of putting it into an int, put it into a long or do what AsusT9 said.

_{Friday, March 22, 2013 5:34 AM}

Show your code.  How do you read this value?

Paul Linton

_{Friday, March 22, 2013 5:46 AM}

Hi ,

 You need handler the INT with Int64.

Signed 32-bit integer range between -2,147,483,648 to 2,147,483,647 only.

http://mysftway.blogspot.com/ 熱誠熱心地幫忙大家！ 希望與大家切磋技術哦~

_{Friday, March 22, 2013 6:38 AM}

My Excel File data looks like below:

I am using the following code to import data from Excel File into a DataTable dt.

dt = new DataTable();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + excelSheet + "]", MyConnection);
MyCommand.TableMappings.Add("Table", "DataSource");
MyCommand.Fill(dt);

After execution of above lines DataTable dt is filled with data as below:



My requirement is to show the data in a DataGrid to user. 

I tried to solve this problem by clone the table and change the CardNo column datatype to Int64. But when I tried to ImportRow() then the above error occurs which is mentioned in my first post. Still I don't have solution to this issue.

_{Friday, March 22, 2013 6:40 AM}

What is the type of the 'CardNo' column in the DataTable.  If it is Decimal then it is just a matter of formatting to get the output you want.

Paul Linton

_{Friday, March 22, 2013 6:52 AM}

After DataTable dt is filled, I noted that both Columns of DataTable dt are string type. But in Excel file CardNo cells format is Number. I think OleDB doesn't consider what type of excel cells and apply System.string data type to all columns.

_{Friday, March 22, 2013 7:06 AM}

Hi,

 Can you trying change the exel format to text.

after fill the source to a datatable ,then just read out the data n cast its.

http://mysftway.blogspot.com/ 熱誠熱心地幫忙大家！ 希望與大家切磋技術哦~

_{Friday, March 22, 2013 7:37 AM}

Yes I already checked it that to convert excel cells format to Text then works fine.

But problem is that my client have its own excel file with huge amount of data and it is hard to change format to Text. Because If I change the format from number to Text then though Excel change the format to Text But at the same time data also converted into e format like 2.1123131e323.

_{Friday, March 22, 2013 9:51 AM}

Thanx Jess KEE, 

I cloned the table and assigned each cell value to the cloned table in a loop. By detecting column by Name I convert the value to Int64 before assigning. Hence this new table is useful for me.

_{Friday, March 22, 2013 10:14 AM}

Suddenly I found that the problem is not solved yet. But a new thing is explored. The above solution of Convert to Int64 works fine but when I re-open the excel file, the problem is raised again. Then I noticed that the above problem occurs only when Excel file is open. But If Excel file is closed, then handling data through C# doesn't create any problem. So it is better solution for this issue that excel file should be closed when reading data through C#.

_{Tuesday, November 18, 2014 1:53 PM}

Well, I know it's too late to answer, but it can help other people.

To read correctly that type of field, just put IMEX=1 in you ConnectionString, like this:

connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\Excel 12.0 Xml;HDR=NO;FirstRowHasNames=0;IMEX=1;TypeGuessRows=0;MaxScanRows=15;ReadOnly=True;\", strCaminhoArquivo);

Doing that you fix the problem reading huge numbers, or...

You also can aply the following Parse():

string strExponentNumber = "8,0003180311E+14";
strExponentNumber = strExponentNumber.Contains("+") ? Int64.Parse(strExponentNumber, System.Globalization.NumberStyles.Float).ToString() : strExponentNumber.Trim();

Hope it help you.

MMJ