Please do let me know if this is a duplicate; I couldn't find an actual answer..
Found this, related one,
Scientific notation when importing from Excel in .Net
But it is several years old
So I am reading an Excel file, into a DataTable with C#.
My code is as follows:
string conStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"", filePath);
System.Data.DataTable fileData = new System.Data.DataTable();
using (OleDbConnection connection = new OleDbConnection(conStr))
{
connection.Open();
if (string.IsNullOrEmpty(sheetName))
{
sheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
}
string selectCmd = string.Format(sqlSelect, sheetName);
using (OleDbCommand command = new OleDbCommand(selectCmd, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
adapter.Fill(fileData);
}
}
So this reads everything in and populates my DataTable
Further through the code I perform validations on some of the content. I'm using RegEx for this.
However, I am running into an issue, where the numbers are being formatted to a scientific notation, or exponential format.
For example, I have 0.000083 and it is being read in as 8.3e-005
So - how can I read in the excel file, and keep the original 0.000083 ?
I'm good with bringing everything in as a string if needed... I've played with the extended properties, but nothing seems to affect how the data is read.
Looking for a solution that negates me having to add each individual cell to the datatable
UPDATE
The other thing I can do, in the meantime, Is convert a scientific-notation to a decimal, and then check that. Though ideally I would like to just bring in without the notation in the first place.
