Number Truncation Issue With Exponential Numbers From Excel

This is a discussion on Number Truncation Issue With Exponential Numbers From Excel within the C# Programming forums, part of the General Programming Boards category; I have a excel xlsx workbook that needs to be loaded into various SQL tables after the file's data has ...

  1. #1
    Registered User
    Join Date
    May 2009
    Posts
    2

    Number Truncation Issue With Exponential Numbers From Excel

    I have a excel xlsx workbook that needs to be loaded into various SQL tables after the file's data has gone though various business logic checks. There can be multiple excel files as input to the program so i'm using the standard connection string and a data adapter to append the entire sheet into a DataTable so all the data can be checked at the same time.

    The code below works for this (sort of).

    Code:
    excelCon = new OleexcelCon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";");
    
    excelCon.Open();
    
    strExcelSheetNames = GetSheetNames(excelCon);
    
    for (int x = 0; x < strExcelSheetNames.Length; x++)
    {                 
        excelCmd.CommandText = "SELECT * FROM [" + strExcelSheetNames[x] + "]";
        excelCmd.CommandType = CommandType.Text;
        excelCmd.Connection = excelCon;
        excelAdapter = new OleDbDataAdapter(excelCmd);
        excelAdapter.Fill(dtExcelConvert);
    }
    I'm having an issue with the number columns however, if the number in the xlsx file is 612561120001 and it's displayed as 6.12561E+11 when i read it from the data table and convert it to a number it appears as 612561120000.

    An example of what i mean is below.

    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.

    Any help on this would be great.

    Thanks
    Attached Images Attached Images  

  2. #2
    Gawking at stupidity
    Join Date
    Jul 2004
    Location
    Oregon, USA
    Posts
    3,163
    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.
    If you understand what you're doing, you're not learning anything.

  3. #3
    Registered User
    Join Date
    Jun 2003
    Posts
    129
    Quote Originally Posted by polydegmon View Post
    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.
    He who asks is a fool for five minutes, but he who does not ask remains a fool forever.

    The fool wonders, the wise man asks. - Benjamin Disraeli

    There are no foolish questions and no man becomes a fool until he has stopped asking questions. Charles Steinmetz

  4. #4
    Registered User C_ntua's Avatar
    Join Date
    Jun 2008
    Posts
    1,853
    Quote Originally Posted by itsme86 View Post
    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?

  5. #5
    (?<!re)tired Mario F.'s Avatar
    Join Date
    May 2006
    Location
    Portugal
    Posts
    7,412
    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.
    Last edited by Mario F.; 03-22-2011 at 04:28 PM.
    The programmer’s wife tells him: “Run to the store and pick up a loaf of bread. If they have eggs, get a dozen.”
    The programmer comes home with 12 loaves of bread.


    Originally Posted by brewbuck:
    Reimplementing a large system in another language to get a 25% performance boost is nonsense. It would be cheaper to just get a computer which is 25% faster.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Find the number of even numbers on even positions
    By teodorrupi in forum C++ Programming
    Replies: 5
    Last Post: 02-27-2011, 10:37 AM
  2. Replies: 4
    Last Post: 03-08-2010, 06:15 PM
  3. xor linked list
    By adramalech in forum C Programming
    Replies: 23
    Last Post: 10-14-2008, 10:13 AM
  4. Number system base M, print numbers N digits wide...
    By biterman in forum C Programming
    Replies: 12
    Last Post: 11-19-2001, 03:31 AM
  5. Array of boolean
    By DMaxJ in forum C++ Programming
    Replies: 11
    Last Post: 10-25-2001, 11:45 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21