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

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.

Quote:

---

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.

Quote:

---

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.