Thread: Number Truncation Issue With Exponential Numbers From Excel

What if you just change the cell format for column V to Number with 0 decimal places? It should display the whole number in the cell.

Originally Posted by polydegmon

I have tried to convert the file to a csv file and read it in but they're comma's in a lot of the fields which cause issues when splitting the data.

Try a tsv (tab seperated values) file instead.

Originally Posted by itsme86

What if you just change the cell format for column V to Number with 0 decimal places? It should display the whole number in the cell.

Alternatively, you can just increase the number of digits and have something like 6.12561120001E+11. Note that even with Number it won't show all digits if the cell width is small, but will show #####.

There is a difference in what it shows and what value it stores internally. How do you read the values in the Excel sheet?

Ok... The value you see at the top in the cell V6 text box is the stored value. Anything else is irrelevant to everyone, except OLEDB (tsk!). Excel is one big mess sometimes, as we can see from how it converted that value into scientific notation (one word: wrong!").

1.
Personally, I avoid reading from Excel if I can. For numerous reasons (none actually to do with this problem). If you convert the file to a csv file, your fields will be separated by semi-colons (and not commas as you seem to believe). So that will deal with your problem. Not that C# also has facilities to read from CSV files through OLEDB, or you can create your own. This is what I use personally. Code it once, build in your utils assembly -- or create an assembly just for it -- and never code it again.

2.
However, not always it is possible to ignore Excel; either because you need to read data from it that can't be translated into csv format, or simply because you can't afford to create a csv file from an excel sheet. For your specific problem, you have two options to solve your problem:

2.1
Format the column to contain raw numbers... you guessed it, format it as text. Any formulas you have in the spreadsheet will still happily work. Those that won't you just need to change them slightly to convert the value to number.

2.2
Alternatively, if you can afford it, don't use a Data table. Process the sheet row by row, using a data reader:

Code:

    /* I apologize for not constructing the connection here. It's been a while and would have to check the documentation... */
    OleDbDataReader dbReader = your_dbCommand.ExecuteReader();

    while (dbReader.Read ()) {
	string value1 = dbReader.GetValue(21).ToString(); // 21 = zero based index of the column you want to read.
	string value2 = dbReader.GetValue(23).ToString(); // 23 = zero based index of the column you want to read.
        /* etc... */
    }

I'm shaken on whether this second option will help you. I seem to remember this option will get you the raw value of the column and ignore the formatted value. So here it is just in case.